Search

Tuesday, October 16, 2012

Row count for all the tables in all the database

Use below query to get the row count for all tables in all the databases:


SET NOCOUNT ON
DECLARE @strQuery VARCHAR(4000)
DECLARE @strTable TABLE (DBName VARCHAR(200), TableName VARCHAR(300), COUNT INT)
SET @strQuery='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 @strTable
EXEC sp_msforeachdb @strQuery
SELECT * FROM @strTable WHERE DBName <> 'tempdb' ORDER BY DBName

No comments:

Post a Comment