sql - 如何为所有表选择表名称、行数和 CHECKSUM_AGG？

Question

我不知道如何为所有表选择 TalbeName、行数、表哈希像这样为所有表选择行数很容易

SELECT sc.name +'.'+ ta.name TableName,SUM(pa.rows) RowCnt
FROM sys.tables ta
INNER JOIN sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY sc.name,ta.name

但是如何另外选择CHECKSUM_AGG(BINARY_CHECKSUM(*))，

获取

TALBENAME_____________ ROWS_________ CHECKSUM_AGG(BINARY_CHECKSUM(*))

提前致谢!

Answer 1

经过3天的搜索，我找到了使用动态SQL的解决方案。

DECLARE @table sysname
DECLARE @sqlCommand nvarchar(MAX)
DECLARE @sqlparam nvarchar(MAX)
DECLARE @COUNT INT
DECLARE @HASH INT

DECLARE TABLE_CURSOR CURSOR FOR
SELECT SC.NAME+'.'+TA.NAME FROM SYS.TABLES TA 
INNER JOIN SYS.SCHEMAS SC ON SC.SCHEMA_ID=TA.SCHEMA_ID ORDER BY 1 
OPEN TABLE_CURSOR 

WHILE 1=1 
BEGIN  
   FETCH TABLE_CURSOR INTO @TABLE
   IF @@FETCH_STATUS<>0
      BREAK     
   SET @sqlCommand = N'SELECT @CNT=COUNT(*),@HSH=CHECKSUM_AGG(BINARY_CHECKSUM(*)) from '+@table
   SET @sqlparam=N'@CNT INT OUTPUT, @HSH INT OUTPUT'
   EXECUTE sp_executesql @sqlCommand, @sqlparam,  @COUNT OUTPUT,  @HASH OUTPUT
   PRINT @TABLE+': '+CONVERT(NVARCHAR(50),@COUNT)+':'+CONVERT(NVARCHAR(50),@HASH)
END
CLOSE TABLE_CURSOR 
DEALLOCATE TABLE_CURSOR