MD3.CompareMasterData.sql 4.72 KB
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'MD3.CompareMasterData') AND type IN (N'P', N'PC'))
DROP PROCEDURE MD3.CompareMasterData
GO


CREATE PROCEDURE MD3.CompareMasterData
	( 
		@NetNewDB SYSNAME,
		@UpgradedDB SYSNAME
    )
    
AS      
BEGIN

/*
	Can run on either @NetNewDB or @UpgradedDB

	Compares table "data" from a given set of "master data" tables that YOU define below

	First it gets a cursory rowcount check
	Then it looks for data discrepancies by using a bin_checksum against the table's key

	Captures data differences that can be output to a text file and examined later.  
	This is typically run as part of a CI build loop.

	It tests an upgraded db's data against a net new db's data for the same build.  
	or
	It tests one db's data against another for differences.
	or
	Run it on today's build (netnew) vs yesterday's build (upgraded) to determine "what has changed in this build"



*/


	SET NOCOUNT ON    
	DECLARE @SQL NVARCHAR(4000)

SET NOCOUNT ON

CREATE TABLE #TableDataToCompare
	(SchemaName varchar(200)
	,TableName varchar(200)
	,KeyCols varchar(200)
	,NetNewRowCnts int
	,PortedRowCnts int
	)


--CHANGEME
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES ('dbo','Foo','ID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES ('dbo','Bar','ID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES ('dbo','Baz','ID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES ('dbo','Qux','ID')



--compare rowcounts
declare @SchemaName varchar(2000), @TableName varchar(2000), @exec_str varchar(max), @keyCols varchar(2000), @SQL_str varchar(max)
declare cnts cursor for 
	select SchemaName, TableName  FROM #TableDataToCompare
open cnts
fetch next from cnts into @SchemaName, @TableName
while @@fetch_status = 0 
begin
	select @exec_str = 'UPDATE #TableDataToCompare SET NetNewRowCnts = (SELECT count(*) FROM ' + @NetNewDB + '.' + @SchemaName + '.' + @TableName + ') WHERE SchemaName = ''' + @SchemaName + ''' AND TableName = ''' + @TableName + ''''
	--select @exec_str
	exec (@exec_str)
	select @exec_str = 'UPDATE #TableDataToCompare SET PortedRowCnts = (SELECT count(*) FROM ' + @UpgradedDB + '.' + @SchemaName + '.' + @TableName + ') WHERE SchemaName = ''' + @SchemaName + ''' AND TableName = ''' + @TableName + ''''
	--select @exec_str
	exec (@exec_str)
	fetch next from cnts into @SchemaName, @TableName
end
close cnts
deallocate cnts

if exists (select * from #TableDataToCompare where NetNewRowCnts <> PortedRowCnts)
BEGIN
	PRINT 'RowCount differences for Master data'
	select * from #TableDataToCompare where NetNewRowCnts <> PortedRowCnts
END
ELSE
BEGIN
	PRINT 'Rowcounts match for Master data'
END

PRINT 'Checking row level data differences...'

DECLARE @bin_list VARCHAR(max) 

--Data-level differences
declare datadiff cursor for 
	select SchemaName, TableName, KeyCols FROM #TableDataToCompare
open datadiff
fetch next from datadiff into @SchemaName, @TableName, @KeyCols
while @@fetch_status = 0 
begin
	select @bin_list = ''
	SELECT @bin_list = COALESCE(@bin_list,'') + c.Name + ','  
	from sys.columns c
	join sys.objects o 
	on c.object_id = o.object_id
	where o.name = @TableName
	and o.type = 'U'
	and o.schema_id = schema_id (@SchemaName)
	and c.name not in ('CreatedBy','CreatedDate','ModifiedBy','ModifiedDate')

	SELECT @bin_list = left(@bin_list,len(@bin_list) - 1)


	--SELECT 'Data Differences for ' + @TableName
	SELECT @SQL_str = '
	(select sum(binary_checksum(' + @bin_list +')) [CheckSum] , ' + @KeyCols + 
	' from ' + @NetNewDB + '.' + @SchemaName + '.' + @TableName +
	' group by ' + @KeyCols + 
	' except ' +
	'select sum(binary_checksum(' + @bin_list +')) [CheckSum] , ' + @KeyCols + 
	' from ' + @UpgradedDB + '.' + @SchemaName + '.' + @TableName +
	' group by ' + @KeyCols + 
	')' +
	'UNION ALL ' +
	'(' +
	'select sum(binary_checksum(' + @bin_list +')) [CheckSum] , ' + @KeyCols + 
	' from ' + @UpgradedDB + '.' + @SchemaName + '.' + @TableName +
	' group by ' + @KeyCols + 
	' except '  + 
	'select sum(binary_checksum(' + @bin_list +')) [CheckSum] , ' + @KeyCols + 
	' from ' + @NetNewDB + '.' + @SchemaName + '.' + @TableName +
	' group by ' + @KeyCols +
	')'
	
	SELECT @exec_str = 'if exists (' + @SQL_str +')
	begin
		PRINT ''Data Differences for ' + @TableName + '''' 
		+ ' PRINT ''Use the following query to determine data difference'' PRINT ''' + @SQL_str + ''''
		 + @SQL_str + '
	end
	'
	--PRINT @exec_str
	EXEC( @exec_str)

	fetch next from datadiff into @SchemaName, @TableName, @KeyCols
end
close datadiff
deallocate datadiff




drop table #TableDataToCompare


	SET NOCOUNT OFF
END

GO