Script to find row count of all tables across multiple databases in SQL Server

We can achieve this by using undocumented stored procedure sp_msforeachdb.




Situations :


1. During DR Test


2. Any database level modifications or object level modifications due to huge load

SET NOCOUNT ON

DECLARE @query VARCHAR(4000) 
DECLARE @temp TABLE (
 DBName VARCHAR(200)
 ,TYPEName VARCHAR(10)
 ,TABLEName VARCHAR(300)
 ,COUNT INT
 )

SET @query = 'SELECT  ''?'',sysobjects.type, sysobjects.Name, sysindexes.Rows 
FROM   ?..sysobjects INNER JOIN ?..sysindexes ON sysobjects.id = sysindexes.id
WHERE  sysindexes.IndId < 2 order by sysobjects.Name'

INSERT @temp
EXEC sp_msforeachdb @query

SELECT *
FROM @temp
WHERE DBName <> 'tempdb'
ORDER BY DBName