Script to get row count for all the tables in all the databases

Ive written this script to get details of row count in all the tables in all the database including the system database. You can modify the script to exclude system database if you dont need details for that. You can see that I havent use COUNT() function to get these values, If I run using this function for all the db then it will take more time and resource.

Applies to:

• SQL Server 2000
• SQL Server 2005
• SQL Server 2008
• SQL Server 2008 R2
• SQL Server 2011

Script

SET NOCOUNT ON
DECLARE @query VARCHAR(4000)
DECLARE @temp TABLE (DBName VARCHAR(200),TABLEName VARCHAR(300), COUNT INT)
SET @query='SELECT  ''?'',sysobjects.Name, sysindexes.Rows
FROM   ?..sysobjects INNER JOIN ?..sysindexes ON sysobjects.id = sysindexes.id
WHERE  type = ''U''  AND sysindexes.IndId < 2 order by sysobjects.Name'
INSERT @temp
EXEC sp_msforeachdb @query
SELECT * FROM @temp WHERE DBName <> 'tempdb' ORDER BY DBName

Output