Fastest way to find the row count of all the tables in SQL

Let me show you 2 simple queries to find the row count of all the tables in a Database.

Some time back I shared a trick to quickly find the row count of a table in SQL Server.  Now lets take it to next level and try to get the row count of all the tables in a Database.  There are 3-4 ways to do that, but I’m going to show you only 2 very identical methods.

1. Using Catalog View sys.partitions:

Here is the script to find the row count of all the tables using sys.partitions.  If you are a normal developer, who doesn’t have high level of permissions on a production Servers, this is the best method for you to find the row count of all the tables quickly.
--------------------------------------------------------------------------------------------------------
SELECT T.NAME AS TableName 
           ,SUM(P.ROWS) AS [RowCount]
FROM sys.partitions AS P
INNER JOIN sys.tables AS T
ON P.OBJECT_ID = T.OBJECT_ID
WHERE P.INDEX_ID < 2
GROUP BY T.NAME
ORDER BY [RowCount] DESC
--------------------------------------------------------------------------------------------------------

2. Using DMV sys.dm_db_partition_stats:

--------------------------------------------------------------------------------------------------------
SELECT T.NAME AS TableName 
           ,SUM(S.ROW_COUNT) AS [RowCount]
FROM sys.dm_db_partition_stats AS S
INNER JOIN sys.tables AS T
ON S.OBJECT_ID = T.OBJECT_ID
WHERE S.INDEX_ID < 2
GROUP BY T.NAME
ORDER BY [RowCount] DESC
--------------------------------------------------------------------------------------------------------

Actually nothing to chose between 2 methods, they both are quite identical, performance wise also both are very same.  Only difference between them is permissions needed to execute them.  While catalog view sys.partitions requires membership in the public role.  Effectively even if you have only “read only” access on a database, you can still use the 1st method.  On the other hand it requires VIEW DATABASE STATE permission to query the sys.dm_db_partition_stats dynamic management view.  So if you are a normal developer “sometimes” it is difficult to get that level of permissions.

And yes, as I already mentioned previously there is a chance that you will get the approximate row count if statistics are not updated.  This applies to both the methods.

To know more about the views see:
1. sys.partitions
2. sys.dm_db_partition_stats

See my couple of other posts on similar topics:
1. Fastest way to find the row count of a table in SQL Server
2. Find the row count of temporary tables in SQL Server

Thanks
Mangal Pardeshi