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

Posted by Blake on 10/26/2018

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;