T-SQL – Find Number of Rows of Database(s) tables Using Undocumented Procedure – sp_MSforeachdb and sp_MSforeachtable

On Technet forum, Op was looking for a way to get table cardinality for all databases in an instance.  There are many ways to get the result. This post gives you an idea and SQL in which undocumented SP’s being called  “sp_MSforeachdb and sp_MSforeachtable”.

The above undocumented SP’s  iterate through each database and each table of a SQL instance

Download SQL :- UndocumentSPToGetNoOfRowsAllDatabases


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:-The above query is expensive. Please use at your risk



The use of IF and Like clause with sp_MSforeachtable

EXEC [sp_MSforeachtable] @command1=N'
IF (N''?'' =''[dbo].[log]'')
 PRINT N''?''
 SELECT COUNT(*) from ? 

EXEC [sp_MSforeachtable] @command1=N'
IF (N''?'' LIKE N''%Resource%]'')
 PRINT N''?''


The use of @whereand parameter to filter any object with sp_MSforeachtable

EXEC sp_msforeachtable
 @command1 ='SELECT ''?'',count(*) FROM ?'
,@whereand = ' And Object_id In (Select Object_id From sys.objects
Where name like ''%EMPLOYEE%'')'


EXEC [sp_MSforeachtable]@command1=N'
 PRINT N''?''
 SELECT ''?'',COUNT(*) FROM ?',
 @whereand = N'AND o.[name] LIKE N''%EMPLOYEE%'''


Example to rebuild ALL indexes of ALL tables of given databases using sp_MSforachdb and sp_MSforachtable

EXEC sp_msforeachdb @command1='
use };
if ''}'' in (''Employee'', ''CellLevelEncryptionDemo'')
print ''}''
exec sp_MSforeachtable @command1=''
ALTER INDEX all ON ? rebuild WITH (FILLFACTOR = 90, sort_in_tempdb = ON);
', @replacechar = '}'

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in sp_MSforeachtable, SQL, T-SQL and tagged , , , , , , , , , , . Bookmark the permalink.

One Response to T-SQL – Find Number of Rows of Database(s) tables Using Undocumented Procedure – sp_MSforeachdb and sp_MSforeachtable

  1. Reblogged this on Prashanth Jayaram and commented:

    Added few more examples

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s