Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Row Count for all Tables in a Database


By:   |   Read Comments (26)   |   Related Tips: More > Scripts

Attend a SQL Server Conference for FREE >> click to learn more


Problem

I am a database tester and one of my tasks involves getting the row counts from all the tables in the source database and comparing it against the corresponding table row counts in the 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 the row counts between source and target databases as part of the testing phase. Getting the row count from each table one by one and comparing and consolidating the results can be a tedious task. Hence any script/solution which can get the row count information from all the tables in a database can be really helpful and effective thereby considerably reducing the effort involved. In this tip we will see four different approaches to get the row counts from all the 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 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.


Approach 3: sp_MSforeachtable System Stored Procedure

sp_MSforeachtable is an undocumented system stored procedure which can be used to iterate through each of the tables in a database. In this approach we will get the row counts from each of the tables in a given database in an iterative fashion and display the record counts for all the tables at once.

The T-SQL query below uses the sp_MSforeachtable system stored procedure to iterate through each of the tables to capture the row count for all the 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 an iterative approach which captures the row count for each of the individual tables, puts them together and displays the results for all the tables.
  2. sp_MSforeachtable is an undocumented system stored procedure.
  3. This approach can be used for testing purposes but it is not recommended for use in any production code. sp_MSforeachtable is an undocumented system stored procedure and may change anytime without prior notification from Microsoft.

Approach 4: COALESCE() Function

The COALESCE() function is used to return 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. Can be used in cases where the number of tables is huge like say few hundred tables. This query can be modified to capture the row counts from a set of tables at one time instead of all the tables which might otherwise put a lot of load on the system.
  2. Can be used even when working with source systems which offer limited privileges such as read-only.

Below is sample output from AdventureWorksDW database. Note - the results in your AdventureWorksDW database might vary slightly from what is displayed below due to various reasons like the version of database being used, any changes/data manipulation done on the database for testing purposes, etc.

AdventureWorks Row Count

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Dattatrey Sindol Datta has 8+ years of experience working with SQL Server BI, Power BI, Microsoft Azure, Azure HDInsight and more.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, June 30, 2017 - 10:18:20 AM - Brian Back To Top

 Your piece, "SQL Server Row Count for all Tables in a Database" is unhelpful for me, because I don't ess how to use any of the techniques. (I tried them all)

Do I need to have a specific node selected in the Object Explorer? SInce my Object Explorer has many databases, how do i use any of these techniques in a SQL Managment Stidio query?

 

Not every one who reads this article are going to have the epertise that you are assuming.

 

 


Friday, June 23, 2017 - 12:56:46 PM - Himanshu Back To Top

 how about this ?

 

declare @string varchar(max)=''
select @string+= 'select ''' + name + '''  as Name,count(*) as count  from  '+ name + '  union all ' from sys.tables
select @string=Substring(@string,1,len(@string)-9)
execute( @string)


Thursday, June 22, 2017 - 8:39:34 AM - Dave Potts Back To Top

It looks like you are looking for a SQL solution but if you want to automate things it may be worth looking at using Powershell in Windows. 

To display the information: 

'Get-ChildItem -Path "SQLSERVER:\SQL\\DEFAULT\Databases\\Tables\" | Select-Object -Property Schema, Name, RowCount | Format-Table -AutoSize' 

This can be easily adapted to store the output as values and compare them:

$a = Get-ChildItem -Path "SQLSERVER:\SQL\\DEFAULT\Databases\\Tables\" | Select-Object -Property Schema, Name, RowCount 

 

$a = Get-ChildItem -Path "SQLSERVER:\SQL\\DEFAULT\Databases\\Tables\" | Select-Object -Property Schema, Name, RowCount 

Compare-Object $a $b

(Note : This uses the SQLPS PowerShell provider. Try 'Import-Module SQLPS' if you get "A drive with the name 'SQLSERVER' does not exist" )

 


Thursday, June 22, 2017 - 7:28:12 AM - Jeff Moden Back To Top

@Vinod,

ALL tables have at least 1 partition.


Thursday, June 22, 2017 - 4:48:12 AM - wilfred van Dijk Back To Top

Additional approach: use column rowcnt in sysindexes:

select sum(rowcnt) from sysindexes where indid < 2

if you want to eliminate system tables, use this query:

select sum(a.rowcnt) from sysindexes a join sys.tables b on a.id = b.object_id where a.indid < 2 and b.is_ms_shipped = 0

 

 

 

 


Sunday, February 26, 2017 - 12:51:38 AM - jayaram Back To Top

 

 Awesome,

 

This query helped me a lot.

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

I also applied like condition by specifiying if the given table name has qos string then only display the count.

Thank you so much for such a beautiful query,

 


Wednesday, May 18, 2016 - 11:30:10 AM - Vinod Back To Top

The above options might all works well in case of tables partitioned / columns indexed. But what about tables where its not partitioned ? The below query simply fetches the record count per table irrespective of volume.

-- query

SELECT  SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name,
i.rows
FROM sys.tables AS t INNER JOIN
sys.sysindexes AS i ON t.object_id = i.id AND i.indid < 2


Friday, August 16, 2013 - 8:19:22 AM - SteveC Back To Top

Very useful article

Thanks for publishing it 

Cheers,

SteveC.


Wednesday, March 06, 2013 - 12:09:56 PM - Jeff Back To Top
You note that you sould not use depricated features, neither should you use undocumented features.
 
 
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

Friday, February 01, 2013 - 11:41:17 AM - komala Back To Top

How to take backup database from sql server


Sunday, January 27, 2013 - 6:38:12 AM - Jimmi Beloitt Back To Top

http://www.bigator.com/2013/01/23/mssql-find-total-records-of-each-table-in-given-database/


Friday, June 22, 2012 - 1:24:02 AM - Dattatrey Sindol (Datta) Back To Top

Hi Murthy,

Your query works as well. Infact the 2nd approach presented above is on these lines with a couple additional filters.

Best Regards,

Dattatrey Sindol (Datta)

http://dattatreysindol.com/


Thursday, June 21, 2012 - 6:51:13 PM - murthy Back To Top

I think we can also using this query :

SELECT DISTINCT s.name as SchemaName,OBJECT_NAME(o.OBJECT_ID) AS TableName,p.row_count
FROM
SYS.objects o JOIN SYS.schemas s
   ON o.schema_id=s.schema_id
        JOIN sys.dm_db_partition_stats p
   ON o.object_id=p.object_id
WHERE o.type LIKE 'U'
AND s.name LIKE 'schema_name'
ORDER BY TableName


Friday, June 08, 2012 - 3:17:33 AM - Prashant Back To Top

@Reckon, your query will not work for table which will not have any PK/index.


Thursday, March 22, 2012 - 1:56:34 PM - Jason Back To Top

I modified sp_spaceused to return correct schema name. In the parsename function, you can recode calling parsename. Imagine you have two objects of the same name in two different schema. That provides row count, space used including index etc.

Regards,

Jason

http://dbace.us

 


Thursday, March 22, 2012 - 1:17:01 AM - Dattatrey Sindol Back To Top

Hi Vipul,

When it comes to views, it gets a little tricky depending on the type of view(s) you are dealing. Based on your preferences mentioned above, I would suggest you use Approach# 4 mentioned above (ofcourse you need to change type = 'U' to type = 'V') with the following considerations in mind:

  • Identify the complexity of the views based on number of tables involved, JOIN conditions, etc. Basically evaluate the complexity based on the T-SQL present inside your view. Group the views into Simple, Medium, & Complex.
  • You could group few views and fire the query in Approach# 4.
  • As the view complexity increases then the number of veiws grouped in each query should be reduced accordingly.
  • In some scenarios based on my experience, source systems expose the data to downstream applications in the form of views which map 1:1 to the underlying tables without any filters/transformations. In this type of scenario, I don't see any much performance impact and Approach# 4 can be used here as well.

Hope that helps.


Monday, March 19, 2012 - 4:00:16 PM - vipul Back To Top

what if I want to capture row counts off of SQL Views?  Don't want to use any deprecated or unsupported functionality.  No MSForeach, or sysindexes.  suggestions?


Monday, November 14, 2011 - 8:56:46 AM - Prabhakar Back To Top

Hello Datta,

Thanks for the info.

Happy coding,

Prabhakar J


Saturday, November 12, 2011 - 8:34:21 AM - Jeremy Kadlec Back To Top

Everyone,

Thank you for all of the comments on this tip.  It is good to see how other SQL Server Professionals in the community address this need.

Thank you,
Jeremy Kadlec


Saturday, November 12, 2011 - 1:18:19 AM - Dattatrey Sindol Back To Top

Hello Adam,

Your approach works well too! The differences I see are storing the results in Temp Table instead of Table Variable, and use of sp_spaceused system stored procedure. However, with the use of sp_spaceused, it does not provide the schema related information.

Thanks for sharing.

 

Best Regards,

Datta


Saturday, November 12, 2011 - 1:08:24 AM - Dattatrey Sindol Back To Top

Hello Prabhakar,

Please note that "sys.sysindexes" is a compatibility view provided for backward compatibility. We should avoid using this as this feature will be removed in future versions of SQL Server.

You can refer to the following URLs for more details:

Best Regards,

Datta


Friday, November 11, 2011 - 5:43:34 PM - Adam M Back To Top

Hello,

I use foreachtable but do it a bit differently. I found count(*) could really take a long time so I used:

 

IF OBJECT_ID('tempdb..#sizeo') IS NOT NULL 

DROP TABLE #sizeo

 

CREATE TABLE #sizeo (

NAME VARCHAR(100),

ROWS INT,

reserved VARCHAR(50),

DATA VARCHAR(50),

index_size VARCHAR(50), 

unused VARCHAR(50)

)

 

EXEC sp_msforeachtable '

insert into #sizeo

EXEC sp_spaceused ''?''

'

SELECT name, ROWS FROM #sizeo

 

I enjoyed the other ways to find it though. Thanks!


Friday, November 11, 2011 - 5:09:28 PM - Prabhakar Back To Top

Hello Friends,

I Reckon, the below query would do the trick.

SELECT DISTINCT OBJECT_NAME(ID),ID,ROWCNT FROM SYSINDEXES WHERE INDID < 2

Regards,

Prabhakar J


Friday, November 11, 2011 - 1:27:30 PM - Dattatrey Sindol Back To Top

Thanks for the feedback GP and posting one more way to get the row count (manual approach).

However, as Jason called out, quite often, we need automatic ways to get row counts for for performing tasks like Auditing, Row Count Validation, etc.

Thank you both for your feedback and additional information.


Friday, November 11, 2011 - 11:58:39 AM - Jason Back To Top

GP, that is manually. We often need this automatic way (insert into a data history, for example). DBA has to do work most efficient way.

Jason

http://usa.redirectme.net


Friday, November 11, 2011 - 7:42:53 AM - GAJANAN Back To Top

very informative

and you would find 5th option pretty simple

- View Object Explorer details
- Click Tables
- and RMC on Headers of the table and Click ROw Count it will give you row count for all the tbale objects.

Thanks
GP


Learn more about SQL Server tools