Query to list the number of rows for each table in a SQL Server database

When working with a SQL Server database, I often find it helpful to have a SQL query to hand that can be executed to tell me how many rows exist within each database table.

This can be useful in scenarios where records are being inserted into lots of tables and you need to check that the row count matches what you were expecting. If you need to purge the existing data before inserting more data, you can use the query to check that the rows have been removed.

This type of query can also be useful for analysing which tables are taking up the most space in your database, by including size statistics alongside the row count in the results.

In this article, I will provide an example query that you can extend according to your specific needs and I’ll show you an example of the results you can expect to see for reference.

The Query

Let’s jump straight into the query which I’ve included within the following sub-section.

The query source can also be found in my SQL Server Scripts GitHub repository, along with several other SQL scripts.

SQL

The SQL query below contains a minimal set of columns that I find most useful for analysis purposes.

-- Example of how to list the number of records for each table in a SQL Server database.
SELECT 
    SCHEMA_NAME(t.[schema_id]) + '.' + t.[name] AS [Table Name],
    p.[rows] AS [Row Count],
    (SUM(a.total_pages) * 8) / 1024 AS [Total Space (MB)],
    (SUM(a.used_pages) * 8) / 1024 AS [Used Space (MB)],
    (SUM(a.data_pages) * 8) / 1024 AS [Data Space (MB)]
FROM sys.tables t
INNER JOIN sys.indexes i ON t.[object_id] = i.[object_id]
INNER JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
WHERE -- Exclude system tables and indexes.
    t.is_ms_shipped = 0 AND
    t.[name] NOT LIKE 'dt%' AND
    i.[object_id] > 255 AND   
    i.index_id <= 1
GROUP BY 
    t.[schema_id], 
    t.[name], 
    p.[rows], 
    i.[object_id]
ORDER BY p.[rows] DESC

The first column that is selected by the query is a combination of the schema name and the table name from the sys.tables system table. Including the schema is very useful, perhaps essential, for databases that contain tables with different schemas. If all tables in the database that you’re working with use the default ‘dbo’ schema, then you may choose to remove the schema from the query.

The row count is selected from the sys.partitions table which is joined to the sys.indexes table.

The next three columns select data from the sys.allocation_units table relating to the amount of space that is being used by the records associated with the table. The amount of space used is shown in megabytes, typically we don’t care about objects that are using less than 1 MB since this is an insignificant amount of data.

The WHERE clause contains a few conditions to make sure that system objects and indexes are excluded from the results, as normally we are not concerned with these in the context of this type of query.

The results must be grouped since the SUM function is being used to aggregate the pages used.

Lastly, the results are ordered by the largest row count, as it is usually most helpful to see tables with the most rows at the top of the results. You may find it helpful to order by the space used or perhaps by the table name instead.

The Results

After executing the query, the results will look similar to the screenshot below. Of course, the actual table names and other values will vary according to the data within your database.

The above results were retrieved from the sample AdventureWorks database.

The table names shown in the results all have the same schema (dbo). However, as the results are ordered by row count you may find that you have a mix of tables from different schemas showing near the top of the list in your results.

If you want to get more analytical you can extend the query to pull additional data from the system tables used in the query and/or join more system tables to give you access to additional data.

Summary

In this article, I demonstrated how you can use a SQL query to report on the number of rows contained within each table in a SQL Server database.

I provided the SQL query listing and walked through it, as well as showcased the kind of results you can expect to see after executing the query in SQL Server Management Studio.

Please feel free to check out my SQL Server Scripts GitHub repository and SQL Archives for other helpful SQL scripts.