CODEWITHSUNDEEP
sql-server
Michael Shnitzer
Michael Shnitzer

Reputation: 2495

Count Rows Across Multiple SQL Server Databases

I'm trying to count the rows of all tables called tblDoc from every database in my SQL Server instance.

I tried this but apparently you can't access or declare variables in cursors:

DECLARE @dbname nvarchar(1000);
DECLARE @sqlCommand NVARCHAR(1000) 
DECLARE @results TABLE(numberOfDocuments bigint);
DECLARE c1 CURSOR READ_ONLY
FOR 
SELECT '[' + name + ']'  FROM sys.databases WHERE name <> 'tempdb' 
OPEN c1 
FETCH NEXT FROM c1 
INTO @dbname 
WHILE @@FETCH_STATUS = 0 
BEGIN 
    SET @sqlCommand = 'INSERT INTO results SELECT COUNT(ID) FROM ' + @dbname + '.dbo.tblDoc;';
    print @sqlCommand;
    EXECUTE sp_executesql @sqlCommand 
FETCH NEXT FROM c1 
INTO @dbname 
END
CLOSE c1 
DEALLOCATE c1 

SELECT COUNT(numberOfDocuments) from @results;

Upvotes: 1

Views: 1757

Answers (2)

Younus Mohammed
Younus Mohammed

Reputation: 175

If you are looking for All table counts from All DB's in a given server then below solution is very simplified.

DECLARE @dbname nvarchar(1000);
DECLARE @sqlCommand NVARCHAR(1000) 
drop table if exists ##temp
create table ##temp (dbname varchar(500),schemaname varchar(500),tablename varchar(500),counts int)
DECLARE c1 CURSOR READ_ONLY
FOR 
SELECT name FROM master.sys.databases  WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');
OPEN c1 
FETCH NEXT FROM c1 
INTO @dbname 
WHILE @@FETCH_STATUS = 0 
BEGIN 
    SET @sqlCommand = 'INSERT INTO ##temp'+'

SELECT 
    
    '''+@dbname+''' as databasename,    
    s.name AS schemaname,
    t.name AS tablename,
    p.rows AS rowcounts

FROM '+@dbname+'.sys.tables t
INNER JOIN '+@dbname+'.sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN '+@dbname+'.sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN '+@dbname+'.sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN '+@dbname+'.sys.schemas s ON t.schema_id = s.schema_id
WHERE t.NAME NOT LIKE ''dt%'' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
    
GROUP BY 
t.name, s.name, p.Rows
'
;
    print @sqlCommand;
    EXECUTE sp_executesql @sqlCommand 
FETCH NEXT FROM c1 
INTO @dbname 
END
CLOSE c1 
DEALLOCATE c1 

select * from ##temp

Upvotes: 0

Raj
Raj

Reputation: 10843

Use Temp table instead

DECLARE @dbname nvarchar(1000);
DECLARE @sqlCommand NVARCHAR(1000) 
CREATE TABLE #results (numberOfDocuments bigint);
DECLARE c1 CURSOR READ_ONLY
FOR 
SELECT '[' + name + ']'  FROM sys.databases WHERE name <> 'tempdb' 
OPEN c1 
FETCH NEXT FROM c1 
INTO @dbname 
WHILE @@FETCH_STATUS = 0 
BEGIN 
    SET @sqlCommand = 'INSERT INTO #results SELECT COUNT(ID) FROM ' + @dbname + '.dbo.tblDoc;';
    print @sqlCommand;
    EXECUTE sp_executesql @sqlCommand 
FETCH NEXT FROM c1 
INTO @dbname 
END
CLOSE c1 
DEALLOCATE c1 

SELECT COUNT(numberOfDocuments) from #results;

Raj

Upvotes: 4

Related Questions