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
SQL:-
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
Output-
The use of IF and Like clause with sp_MSforeachtable
EXEC [sp_MSforeachtable] @command1=N' IF (N''?'' =''[dbo].[log]'') BEGIN PRINT N''?'' SELECT COUNT(*) from ? END'
EXEC [sp_MSforeachtable] @command1=N' IF (N''?'' LIKE N''%Resource%]'') BEGIN PRINT N''?'' SELECT ''?'' TABLENAME,COUNT(*) CNT from ? END '
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%'')' OR 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'') begin print ''}'' exec sp_MSforeachtable @command1='' ALTER INDEX all ON ? rebuild WITH (FILLFACTOR = 90, sort_in_tempdb = ON); '' end ', @replacechar = '}'