Where is my data? How to quickly determine where the important data lives in a SQL server database.

Every time I start analysing a database, I want to know which are the key tables.  Typically this means knowing which tables have the most records in them – this is simple to find out, and if you then visualise the data, its possible to immediately see how the data is clustered throughout your database.

How to???  You should be able to do this in 2 minutes…

Start by running this query against your database – in SQL server:

select * from INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE <> 'VIEW'

order by table_name

This gives the following result from Microsoft’s Dynamics CRM database:

Now copy the contents of the table name column, and paste into the first column of an Excel spreadsheet.

Then add the following function to column B of the same spreadsheet, and fill the cells next to the table names by dragging the function down the Excel column.

=CONCATENATE("select '",A1,"' AS TableName ,count(*) AS RecordCount from ", A1, " UNION")

Your EXCEL sheet should now look something like this:

Now copy the contents of column B back to SQL management studio – removing the UNION keyword from the last statement and hit F5.

This should provide a list of tables with their record counts, like this:

You might choose to stop here, but I like to cut and paste the data into Tableau, and then use a horizontal ranked bar chart to really see where the data is – you should end up with something like this:

Enjoy

Tom.

Comments

[Kris]

Tom,

Take a look at the undocumented SQL Server stored procedure sp_msforeachtable which does this in one step. Very useful!

Kris

[Tom Brown]

Or you could run this:

SELECT LEFT(OBJECT_NAME(si.id), 40) AS [Table Name],
rowcnt AS [Estimated Number of Rows]
FROM sysindexes si WITH (NOLOCK)
INNER JOIN sysobjects so WITH (NOLOCK) ON si.id = so.id
WHERE si.indid < 2 AND so.xtype = 'U'
ORDER BY LEFT(OBJECT_NAME(si.id), 40)