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
Leave a Reply