Thursday, July 14, 2011

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)

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


  1. Nice Article !

    Really this will help to people of SQL Server Community.
    I have also prepared small note on this, find the total row count for all table of SQL Server.

  2. Hi, Great.. Tutorial is just awesome..It is really helpful for a newbie like me.. I am a regular follower of your blog. Really very informative post you shared here. Kindly keep blogging. If anyone wants to become a .Net developer learn from .Net Core Training in Chennai. or learn thru .Net Core Training in Chennai. Nowadays Dot Net has tons of job opportunities on various vertical industry.
    or Es6 Training in Chennai. Nowadays JavaScript has tons of job opportunities on various vertical industry.