SQL Server Row Count for all Tables in a Database

Problem

I need row counts from all tables in a SQL Server database to compare against table row counts in a target database. How do I get the row counts from all the tables in a SQL Server database? What are the different approaches to get this information?  Check out this tip to get these questions and more answered.

Solution

It is a common step in any ETL project to validate row counts between source and target databases as part of testing. Getting the row count from each table one by one and comparing and consolidating the results can be a tedious task. Scripting this task as much as possible is the ideal solution.

In this tip, we will see four approaches to get row counts from all tables in a SQL Server database.

Let’s take a look at each of the approaches:

• sys.partitions Catalog View
• sys.dm_db_partition_stats Dynamic Management View (DMV)
• sp_MSforeachtable System Stored Procedure
• COALESCE() Function

Approach 1: sys.partitions Catalog View

sys.partitions is an Object Catalog View and contains one row for each partition for each table and most indexes (Except Fulltext, Spatial, and XML indexes). At least one partition exists per table (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. Use 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. This displays 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.

Approach 3: sp_MSforeachtable System Stored Procedure

sp_MSforeachtable is an undocumented system stored procedure to iterate through each table in a database. We can get row counts for each table in a database and display the record counts for all the tables.

The query below uses sp_MSforeachtable to iterate through each table to capture the row counts for all tables in a database.

DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowCount] INT) ;
INSERT INTO @TableRowCounts ([TableName], [RowCount])
EXEC sp_MSforeachtable 'SELECT ''?'' [TableName], COUNT(*) [RowCount] FROM ?' ;
SELECT [TableName], [RowCount]
FROM @TableRowCounts
ORDER BY [TableName]
GO

Below are the highlights of this approach:

1. This is iterative, captures the row count for each table and displays the results for all tables.
2. sp_MSforeachtable is an undocumented system stored procedure.
3. Use for testing but not recommended for production since sp_MSforeachtable is undocumented and can change at any time.

Approach 4: COALESCE() Function

The COALESCE() function returns the first non-NULL value/expression among its arguments. In this approach we will build a query to get the row count from each of the individual tables with UNION ALL to combine the results and run the entire query.

The T-SQL query below uses the COALESCE() function to iterate through each of the tables to dynamically build a query to capture the row count from each of the tables (individual COUNT queries combined using UNION ALL) and provides the row counts for all the tables in a database.

DECLARE @QueryString NVARCHAR(MAX) ;
SELECT @QueryString = COALESCE(@QueryString + ' UNION ALL ','')
                      + 'SELECT '
                      + '''' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                      + '.' + QUOTENAME(sOBJ.name) + '''' + ' AS [TableName]
                      , COUNT(*) AS [RowCount] FROM '
                      + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                      + '.' + QUOTENAME(sOBJ.name) + ' WITH (NOLOCK) '
FROM sys.objects AS sOBJ
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name ;
EXEC sp_executesql @QueryString
GO

Below are the highlights of this approach:

1. Use where the number of tables is huge like say a few hundred tables.
2. You can modify the query to capture row counts for a set of tables instead of all the tables.
3. Use with source systems which offer limited privileges such as read-only.

Sample Output

Below is sample output from AdventureWorksDW database. Note – your results might be different due to database changes.

Next Steps

• Review the scripts/approaches in this tips to see which approach suits the best for your scenario.
• Check out these additional resources:
  • Catalog Views
  • Dynamic Management Views and Functions

Dattatrey Sindol

Dattatrey Sindol (aka Datta) is a Business Intelligence enthusiast, passionate developer, and blogger. He started his career in November 2006 and since then has worked on various technologies including SQL Server BI, Power BI, Microsoft Azure, & Azure HDInsight within Microsoft Stack and other Cloud & Big Data technologies outside Microsoft Stack. Currently, he is working as an Associate BI Architect at a leading IT Services company in India (Bangalore).  Datta also is a Microsoft Certified IT Professional (MCITP) in SQL Server Business Intelligence 2008.

• MSSQLTips Awards: Trendsetter (25+ tips) – 2014 | Author Contender – 2014