我有一个包含数据库、模式和表名的表。我想使用groupby for defined column为同一个表中的每一行计算行数。
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我曾尝试使用动态sql，但由于8000个字符的限制，没有显示完整的查询。
1条答案
按热度按时间ehxuflar1#
可以使用未记录的存储过程sp\u msforeachtable对数据库中的每个表运行，以获取行计数。但是，由于它是未记录的存储过程，将来可能会被删除。
从您的问题，我知道您希望行数在表级别。下面的代码在表级别生成行计数并插入到表中。
如果要对不同的数据库运行它，请对包含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