SQL Server – Count table rows for all databases using sp_MSforeachdb and sp_MSforeachtable

This post is inspired from one of other post in SQL Server Geeks by Ahmad Osama.

http://www.sqlservergeeks.com/sql-server-sp_spaceused-returns-wrong-count/

I would like to extend some of my ideas further to get the table count as below. At times, SQL Server developers/DBAs might need to know the table row count for all tables from all databases available on a server. There are various approaches to get the row counts in SQL Server.

Here are few approaches as below:
Approach 1:

DECLARE @TableRowCounts TABLE ([databaseNAme] Varchar(100),[TableName] VARCHAR(128), [RowCount] INT) ;
	INSERT INTO @TableRowCounts ([databaseNAme],[TableName], [RowCount])
	EXEC sp_MSforeachdb 'select ''?'' as database_name,o.name,max(i.rowcnt )
						  From sys.objects o 
						inner join sys.sysindexes i on o.object_id=i.id 
						where o.type=''U'' 
						group by o.name' ;
						
Select * From @TableRowCounts

For Partition tables, the above query can be changed a bit as below:
Transact-SQL

DECLARE @TableRowCounts TABLE ([databaseNAme] Varchar(100),[TableName] VARCHAR(128), [RowCount] INT) ;
	INSERT INTO @TableRowCounts ([databaseNAme],[TableName], [RowCount])
	EXEC sp_MSforeachdb 'SELECT ''?'',TBL.name, SUM(PART.rows) AS rows
						FROM sys.tables TBL
						INNER JOIN sys.partitions PART ON TBL.object_id = PART.object_id
						INNER JOIN sys.indexes IDX ON PART.object_id = IDX.object_id
						AND PART.index_id = IDX.index_id
						WHERE IDX.index_id < 2
						GROUP BY TBL.object_id, TBL.name;' ;
						
Select * From @TableRowCounts

Approach 2:

DECLARE @TableRowCounts TABLE ([databaseNAme] Varchar(100),[TableName] VARCHAR(128), [RowCount] INT) ;
	INSERT INTO @TableRowCounts ([databaseNAme],[TableName], [RowCount])
	
EXEC sp_MSforeachdb 'SELECT ''?'',OBJECT_NAME(object_id), SUM(row_count) AS rows
FROM sys.dm_db_partition_stats
WHERE index_id < 2
GROUP BY OBJECT_NAME(object_id)' ;

Select * From @TableRowCounts

Likewise, there are still more ways to get the info in SQL Server. I do not really want to list all the methods, as a simple google search would end up with a lots of scripts for the same. Rather, want to deal about one of major drawbacks with such approaches is it may or may not be accurate in value. If you look at the MSDN articles for each catalogs used in the above approaches, you can see that it clearly suggests based on the approximate values. So if we are looking for an accurate value for tables that are undergoing frequent DELETE/INSERT, then we should really rely on COUNT() function. As COUNT function reads the data, the row count would be more accurate than any other methods.

Here is my attempt to get the count using sp_MSforeachdb and sp_MSforeachtable. I tried to make the script as simple as possible. You may have a look at below:

create Table  TableRowCounts  ([databaseNAme] Varchar(100),[TableName] VARCHAR(128), [RowCount] INT) ;
 
EXEC sp_MSforeachdb
@command1 = 'IF not exists(select 1 where ''?'' in (''master'',''model'',''msdb'',''tempdb''))
				INSERT INTO TableRowCounts ([databaseNAme],[TableName], [RowCount])
				EXEC [?].dbo.sp_MSforeachtable 
				@command1 = ''SELECT ''''?'''',''''&'''',count(1) FROM &'',
					@replacechar = ''&'''

Select * From TableRowCounts
					
Drop Table TableRowCounts
 

Note: You should be very careful as its a very expensive query, you may use at your own risk.Both sp_MSforEachDB and sp_MSforEachtable are undocumented as well.

Hope you enjoyed this post, please share your thoughts on the same.