November 1, 2016 at 3:34 am
What I want to achieve here is to get the result from the CheckSum_Agg(Binary_CheckSum(*)) and write this to a table with the matching TABLE_NAME.
The result that I get now is something like:
SELECT CheckSum_Agg(Binary_CheckSum(*)) AS HashTable FROM DatabaseX.dbo.tblX A desirable result would be the checksum value itself, something like 1971451.
The query below is something I came up with, but it's missing the next step to write the checksum result to a table:
DROP TABLE HashTable
CREATE TABLE HashTable
(
HashTable nvarchar (255),
TABLE_NAME nvarchar (255)
)
INSERT HashTable
EXEC master..sp_MSForeachdb 'USE [?]
IF DB_ID(''?'') > 4
SELECT ''SELECT CheckSum_Agg(Binary_CheckSum(*)) AS HashTable FROM '' + TABLE_CATALOG + ''.'' + ''dbo'' + ''.'' + TABLE_NAME AS HashTable
,TABLE_NAME AS TABLE_NAME
FROM ?.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE''
'
Thanks,
November 1, 2016 at 3:47 am
sp_MSForeachdb will return one result set for each database and therefore you can't put the results into your destination table with a single INSERT statement. You'll need to embed the INSERT statement in your dynamic SQL.
John
November 1, 2016 at 8:22 am
DECLARE @HashTable TABLE(HashTable nvarchar (255),TABLE_NAME nvarchar (255));
INSERT @HashTable
EXECUTE sp_MSForeachdb 'IF DB_ID(''$'') > 4 BEGIN; USE [$]; EXECUTE sp_MSForeachTable ''SELECT CheckSum_Agg(Binary_CheckSum(*)) AS HashTable, ''''[$].?'''' AS TableName FROM [$].?''; END','$';
SELECT * FROM @HashTable
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply