SQL Server: Row counts for all tables via a single query

The following is an example of a T-SQL query that will quickly return the number of records in each table in a database. One of the scenarios where I have found this useful is when you need to query for the counts in a list of tables but that source comes from somewhere that isn't hard coded (say, another table where you can now do a sub query or a join to only select for those tables).

SELECT SCHEMA_NAME(schema_id) AS [schema_name],
    t.name AS [table_name],
SUM(p.rows) AS [row_count]
FROM sys.tables AS [t]
INNER JOIN sys.partitions AS [p]
    ON t.[object_id] = p.[object_id] AND p.index_id IN (0, 1)
GROUP BY SCHEMA_NAME(schema_id), [t].name;

Leave a comment

Please note that we won't show your email to others, or use it for sending unwanted emails. We will only use it to render your Gravatar image and to validate you as a real person.