How to get row count from Different Databases. Database Names, Schema Names and Table Names, stored in SQL Table

user_5167587 0
Viewed 78

I have table that contains Database, Schema, and Table Name. I would like to count the row for each rows using group by for defined column in Same Table. 

DECLARE @WeeklyRowCount TABLE
(
    db VARCHAR(50)
    ,sch VARCHAR(15)
    ,Tb VARCHAR(100)
    ,col VARCHAR(50)    
)

DECLARE @cmd VARCHAR(MAX) = (
SELECT ' UNION ALL SELECT COUNT(*) AS C, DATE_STAMP,''' 
     + QUOTENAME(sch) + '.' + QUOTENAME(tb) 
    + ''' AS T FROM ' + QUOTENAME(db) + '.' + QUOTENAME(sch) + '.' + QUOTENAME(tb) + 'GROUP BY col'
    FROM @WeeklyRowCount )

SELECT COUNT(*) AS C, col ,'Table name' AS T FROM db.sch.tb GROUP BY col

I have tried to use dynamic SQL but due to limitation of 8000 character, full query is not showing up.

tsql sql-server sql
1 Answers

You can use undocumented stored procedure sp_msforeachtable to run against each table in the database, to get the row count. But, as it is undocumented stored procedure, in future, it might be removed.

From your question, I understand you want rowcount at table level. The below code generates row count at table level and inserts into table. 

CREATE table WeeklyRowCount 
(
    db VARCHAR(50)
    ,sch VARCHAR(15)
    ,Tb VARCHAR(120)
    ,rcnt VARCHAR(50)    
)


exec sp_MSforeachtable ' INSERT INTO WeeklyRowCount(db,sch,tb,col) SELECT db_name() as db,OBJECT_SCHEMA_NAME(OBJECT_ID(''?'')) as sch, ''?'' as tb, count(1) as rcnt  from ? '

SELECT * FROM WeeklyRowCount

If you want to run it againt different databases, Use three part identifier for the table containing rowcount WeeklyRowcount

USE dbName
GO
CREATE table WeeklyRowCount 
(
    db VARCHAR(50)
    ,sch VARCHAR(15)
    ,Tb VARCHAR(120)
    ,rcnt VARCHAR(50)    
)

USE DbName1
go

exec sp_MSforeachtable ' INSERT INTO dbname.dbo.WeeklyRowCount(db,sch,tb,col) SELECT db_name() as db,OBJECT_SCHEMA_NAME(OBJECT_ID(''?'')) as sch, ''?'' as tb, count(1) as rcnt  from ? '

use DbName2
go

exec sp_MSforeachtable ' INSERT INTO dbname.dbo.WeeklyRowCount(db,sch,tb,col) SELECT db_name() as db,OBJECT_SCHEMA_NAME(OBJECT_ID(''?'')) as sch, ''?'' as tb, count(1) as rcnt  from ? '

SELECT * FROM dbname.dbo.WeeklyRowCount

For more information about sp_msforeachtable

user_6349350
Related