13Jun/09
Script to get row count for all the tables in all the databases
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
April 13th, 2010 - 04:01
I get the following while executing your script in SQL 2000
EXECUTE cannot be used as a source when inserting into a table variable.
April 13th, 2010 - 04:20
Some times it happens in sql2k when it has more rows and also for other reasons. I’ve replaced table variable with temp table, now it should run fine, try the query below
SET NOCOUNT ONDECLARE @query VARCHAR(4000)
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE name ='##temp')
DROP TABLE ##temp
CREATE TABLE ##temp (DBName VARCHAR(200),TABLEName VARCHAR(300), COUNT INT)
SET @query='SELECT ''?'',SO.Name, SI.Rows
FROM ?..sysobjects AS SO INNER JOIN ?..sysindexes AS SI ON SO.id = SI.id
WHERE SO.type = ''U'' AND SI.IndId < 2 order by SO.Name'
INSERT ##temp
EXEC sp_msforeachdb @query
SELECT * FROM ##temp WHERE DBName 'tempdb' ORDER BY DBName
DROP TABLE ##temp