CODEWITHSUNDEEP
sqlsql-serverselect
LightTechnician
LightTechnician

Reputation: 287

How do i select Tables name and Rows count and CHECKSUM_AGG for all tables?

I can't figure out how to select TalbeName,Rows Count,Table Hash for all tables it is easy to select Rows count for all tables like this

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

But how to select CHECKSUM_AGG(BINARY_CHECKSUM(*)) in addition,

to get

TALBENAME_____________ ROWS_________ CHECKSUM_AGG(BINARY_CHECKSUM(*))

Thanks in advance!.

Upvotes: 3

Views: 283

Answers (1)

LightTechnician
LightTechnician

Reputation: 287

After 3 days of searching, I Found the solution using Dynamic 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

Upvotes: 2

Related Questions