Get row counts for all tables in all databases in SQL Server

Question

Dear Experts,

Could you please share any script that will provide list of all the row counts of all the tables in all the databases in SQL Server 2019.

I know we can get row count of all the tables in a database. But this is for all the databases as well at once.

Thanks,

Answer 1

I know we can get row count of all the tables in a database. But this is for all the databases as well at once.

Attached is an example script to get row counts for tables in all databases in one go. This excludes system databases and secondary replicas.

I had trouble with the T-SQL code formating so I included the script as an attachment along with an image of the code below.

256707-for-each-database.txt

Answer 2

You can query DMV sys.dm_db_partition_stats (Transact-SQL) to get the row count for each table
If you want to get it for all databases, then you have to query it on every database.
Sample code:

SELECT DISTINCT SCH.name AS SchemaName  
      ,OBJ.name AS ObjName  
      ,OBJ.type_desc AS ObjType  
      ,INDX.name AS IndexName  
      ,INDX.type_desc AS IndexType  
      ,PART.partition_number AS PartitionNumber  
      ,PART.rows AS PartitionRows  
      ,STAT.row_count AS StatRowCount  
      ,STAT.used_page_count * 8 AS UsedSizeKB  
      ,STAT.reserved_page_count * 8 AS ReservedSizeKB  
      ,PART.data_compression_desc  
      ,DS.name AS FilegroupName  
      ,(STAT.reserved_page_count - STAT.used_page_count) * 8 AS Unused  
FROM sys.partitions AS PART  
     INNER JOIN sys.dm_db_partition_stats AS STAT  
         ON PART.partition_id = STAT.partition_id  
            AND PART.partition_number = STAT.partition_number  
     INNER JOIN sys.objects AS OBJ  
         ON STAT.object_id = OBJ.object_id  
     INNER JOIN sys.schemas AS SCH  
         ON OBJ.schema_id = SCH.schema_id  
     INNER JOIN sys.indexes AS INDX  
         ON STAT.object_id = INDX.object_id  
            AND STAT.index_id = INDX.index_id  
     INNER JOIN sys.data_spaces AS DS  
         ON INDX.data_space_id = DS.data_space_id  
ORDER BY OBJ.name  
        ,INDX.name  
        ,PART.partition_number  

Answer 3

Hi @mo boy ,

Approach 1: sys.partitions Catalog View
sys.partitions is an Object Catalog View and contains one row for each partition of each of the tables and most types of indexes (Except Fulltext, Spatial, and XML indexes). Every table in SQL Server contains at least one partition (default partition) even if the table is not explicitly partitioned.

The T-SQL query below uses the sys.partitions catalog view to capture the row counts for all tables in a database.

SELECT  
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]  
      , SUM(sPTN.Rows) AS [RowCount]  
FROM   
      sys.objects AS sOBJ  
      INNER JOIN sys.partitions AS sPTN  
            ON sOBJ.object_id = sPTN.object_id  
WHERE  
      sOBJ.type = 'U'  
      AND sOBJ.is_ms_shipped = 0x0  
      AND index_id < 2 -- 0:Heap, 1:Clustered  
GROUP BY   
      sOBJ.schema_id  
      , sOBJ.name  
ORDER BY [TableName]  
GO  

Below are the highlights of this approach:

1. Requires membership in the public role.
2. Can be used even when working with source systems which offer limited privileges such as read-only.

Approach 2: sys.dm_db_partition_stats Dynamic Management View (DMV)
sys.dm_db_partition_stats is a Dynamic Management View (DMV) which contains one row per partition and displays the information about the space used to store and manage different data allocation unit types - IN_ROW_DATA, LOB_DATA and ROW_OVERFLOW_DATA.

The T-SQL query below uses the sys.dm_db_partition_stats DMV to capture the row counts for all tables in a database.

SELECT  
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]  
      , SUM(sdmvPTNS.row_count) AS [RowCount]  
FROM  
      sys.objects AS sOBJ  
      INNER JOIN sys.dm_db_partition_stats AS sdmvPTNS  
            ON sOBJ.object_id = sdmvPTNS.object_id  
WHERE   
      sOBJ.type = 'U'  
      AND sOBJ.is_ms_shipped = 0x0  
      AND sdmvPTNS.index_id < 2  
GROUP BY  
      sOBJ.schema_id  
      , sOBJ.name  
ORDER BY [TableName]  
GO  

Below are the highlights of this approach:

1. VIEW DATABASE STATE permissions are required in the database.
2. The values in the sys.dm_db_partition_stats DMV are reset on server restart or when an object/partition is dropped and recreated.

In general, querying the Dynamic Management Views (DMVs), requires VIEW SERVER STATE or VIEW DATABASE STATE permissions based on the Dynamic Management View/Function which is being queried.

Best regards
Niko

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".