SQL Server 2005/2008: Get row counts on all the tables.

If you have to count the number of records on all the tables then there are two ways to do that. The first one is the more efficient one and you have to query the system tables to get that info.


SELECT  SCHEMA_NAME(t.schema_id) AS Schema_Name, t.name AS Table_Name, 
i.rows as Row_Count
FROM sys.tables AS t INNER JOIN
sys.sysindexes AS i ON t.object_id = i.id AND i.indid < 2
order by i.rows desc

But if you want to SELECT count(*) from all the tables then use the following query.


CREATE TABLE #counts
(     table_name varchar(255),
    row_count int )
EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'

SELECT table_name, row_count FROM #counts ORDER BY  row_count DESC
DROP table #counts

I prefer the first method since that does not involve selecting from each table.