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

Question

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.

EXEC (@cmd) does not seem to have the 8000 char limitation. learn.microsoft.com/en-us/sql/t-sql/language-elements/… No need to PRINT the generated statement — devio
– devio, Commented Jun 1, 2020 at 5:50

Venkataraman R · Accepted Answer · 2020-06-01 04:48:01Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related