Monday, March 12, 2012

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

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

No comments: