TSQL Query to List Tables in a Database with Row Counts

In a previous post I talked about how to find highly populated tables in a database. I’m going to expand on that in this post to show how to catalog a database into data-holding tables and supporting details tables.

Overview

If you are ever looking at an unfamiliar database, it can be handy to run a SQL query that helps you to define what tables are main data storing tables, and what tables are supporting details tables.

How is this going to work? We can get our overview by assuming that details tables usually consist of 10 or fewer entries, compared to main data tables that usually each have a larger amount of data. If you run with this concept, it can give you an instant easy head start into understanding how a database is structured.

How we’re going to solve the problem

So the trick here is to loop through each of the tables in a database and get the number of data rows in each table. Also, ordering the results will let us know what are main data tables and what are smaller data tables.

Last time I looked into this I went ahead and wrote a cursor against the sys.tables system table. This is a straightforward way of getting the job done, but if you want to cut down on code complexity and the number of lines of code, you can consider using the sp_MSForEachTable system procedure. This procedure is considered undocumented, but it has been around for a while now, so I don’t think it’s going to vanish anytime soon.

Then we can mix in the sp_spaceused system procedure that will return the number of rows for each of the tables that sp_MSForEachTable returns. If we save all of this to a temporary table, we can process and filter the results as we like.

The Example Code:

Here is the query that is set to:

1. The first set of results will return a set of descending-order results for tables with more than ten rows. This means the top returned tables will be the most important data-holding tables.
2. The second set of results holds tables with 10 and fewer rows. You can probably safely bet that the tables in this set of results are the supporting details tables of the database

As you can see in the code below, we first create a temporary table to hold the results of executing sp_SpaceUsed. Then we execute sp_MSForEachTable to loop through each table in the database and call the sp_SpaceUsed procedure to get the row counts that we need. Notice also that the query uses the question mark syntax of a parameterized query as part of the call to execute sp_SpaceUsed.

CREATE TABLE #TableList(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TableName] [VARCHAR] (150) NOT NULL,
    [RowCount] [VARCHAR] (20) NULL, 
    [ReservedSpace] [VARCHAR] (20) NULL, 
    [Data] [VARCHAR] (20) NULL, 
    [IndexSize] [VARCHAR] (20) NULL, 
    [Unused] [VARCHAR] (20) NULL
)
GO
EXEC sp_MSForEachTable 'INSERT INTO #TableList EXEC sp_spaceused ''?'' '
SELECT * FROM #TableList WHERE CAST([RowCount] AS INT) > 10 ORDER BY CAST([RowCount] AS INT) DESC 
SELECT * FROM #TableList WHERE CAST([RowCount] AS INT) <= 10
DROP TABLE #TableList