Script to get row count for all the tables in all the databases
June 13th, 2009
I’ve 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 don’t need details for that. You can see that I haven’t 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
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
