DBA_CompareDBData.sql 8.1 KB
Newer Older
dwentzel's avatar
dwentzel committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167
IF OBJECT_ID('DBA_CompareDBData') IS NOT NULL 
  DROP PROCEDURE DBA_CompareDBData
GO
/*
quick notes:
	compares "data" from a given set of "model data" tables...defined below
	looks for row discrepancies by table key
issues:
exec DBA_CompareDBData 'MaxAppDB','Alleg'	
	

*/


 
 CREATE PROCEDURE DBA_CompareDBData
  (
    @NetNewDB SYSNAME
  , @PortedDB SYSNAME
  )
AS 
BEGIN

SET NOCOUNT ON
DECLARE @SQL NVARCHAR(4000)

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

INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES ('dbo','tblData','ID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('dbo','tblProperties','ID')
--INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('dbo','tblTemplates','ID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','AppFunction','AppFunctionID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','AppFunctionGroup','AppFunctionGroupID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','AppFunctionXAppFunction','AppFunctionXAppFunctionID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','AppFunctionXAppFunctionGroup','AppFunctionXAppFunctionGroupID')
--INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','AppUser','AppUserID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','CodeDefinition','CodeDefinitionID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','CodeDisplay','CodeDefinitionID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','ContactType','ContactTypeID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','JournalType','JournalTypeID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','LicenseRole','LicenseRoleID')
--INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','NextId','ID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','PayrollCalculation','PayrollCalculationID')
--INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','Person','PersonID')
--INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','SecurityEntity','SecurityEntityID')
--INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','SecurityEntityXAppFunction','SecurityEntityXAppFunctionID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','StateProvince','StateProvinceID')
--INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','SystemSetting','SystemSettingID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','UIEntityListConfiguration','UIEntityListConfigurationID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','UIListDetail','UIListDetailID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','UIListHeader','UIListHeaderID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','UIListHeaderXAppFunction','UIListHeaderXAppFunctionID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','UnitOfMeasure','UnitOfMeasureID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','WitholdingStatusType','WitholdingStatusTypeID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','ObjectField','ObjectFieldID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','ObjectFieldXField','ObjectFieldXFieldID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','ObjectParameter','ObjectParameterID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','ObjectXObject','ObjectXObjectID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','ObjectXObjectField','ObjectXObjectFieldID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','ReportDefinitionXReportDefinition','ReportDefinitionXReportDefinitionID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','ReportObjectField','ReportObjectFieldID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','ReportParameterDefinition','ReportParameterDefinitionID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','ReportParameterDefinitionXObjectField','ReportParameterDefinitionXObjectFieldID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','ReportParameterXParameter','ReportParameterXParameterID')
INSERT INTO #TableDataToCompare (Schemaname, TableName,KeyCols) VALUES  ('MXW','ReportDefinition','ReportDefinitionID')


--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 ' + @PortedDB + '.' + @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 ' + @PortedDB + '.' + @SchemaName + '.' + @TableName +
	' group by ' + @KeyCols + 
	')' +
	'UNION ALL ' +
	'(' +
	'select sum(binary_checksum(' + @bin_list +')) [CheckSum] , ' + @KeyCols + 
	' from ' + @PortedDB + '.' + @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
END