How to get table row count for all tables in a database in T-SQL

The following sql query displays all the tables in a database sorted by their rowcount (largest first)

SELECT o.name, 
 ddps.row_count 
FROM sys.indexes AS i 
 INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID 
 INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID 
 AND i.index_id = ddps.index_id 
WHERE i.index_id < 2 
 AND o.is_ms_shipped = 0 
ORDER BY row_count DESC

The elapsed time for the above query on the Adventure Works database was only 3ms!! While if we use the COUNT function on the largest table in the database

SELECT COUNT(*) FROM Sales.SalesOrderDetail

The above query took 12ms to get the count for just one table. Obviously, the biggest limitation on using the former approach is that you can't filter data using a where clause but if you need to count all the records, i suggest trying the first query :)

For more details, check this detailed blog entry