Donate to support site

This is to test the output of changes made to a system.
The framework consists of two stored procedures
	s_RegressionTest_SaveData
	s_RegressionTest_CompareData

Before running updates to the system call s_RegressionTest_SaveData with the table name to check (can be run multip[le times for different tables
	exec s_RegressionTest_SaveData 'MyTable'
This copies the table to MyTable_z_test

Then run the updated system

Now run s_RegressionTest_CompareData
This has a few parameters
	e.g. exec s_RegressionTest_CompareData 'MyTable', 'MyTable_Identity' ,'N' ,1
		Parameters
				MyTable 			Name of saved table to check for changes
				MyTable_Identity	Name of column to ignore - e.g. if a table is repopulated ignore identity values. Easy to change this for multiple ignore columns.
				N					Don't use the primary key as the unique index for compare e.g. if it is an identity which has changed
				1					1 = show debuf info, 0 = don't show debug info

The compare will look at each row in the table and compare each column for differences between the new and old table versions
The output will be the top 100 rows of differences from each table. Search for top 100 if you want to change this.
The output will be a single dataset containing rows from the old and new table. The first column will contain the source table name.




if object_id('s_RegressionTest_SaveData', 'P') is not null
	drop proc s_RegressionTest_SaveData
go
create proc s_RegressionTest_SaveData
@TableToCheck varchar(200) = 'MyTable'
as
/*
exec s_RegressionTest_SaveData 'MyTable'
*/
declare @TestTable varchar(210)
select @TestTable = @TableToCheck + '_z_test'
declare @sql varchar(max)

-- drop existing test table
select @sql = 'if object_id(''' + @TestTable + ''',''U'') is not null drop table ' + @TestTable
--select @sql
exec (@sql)

-- Save existing data to test table
select @sql = 'select * into ' + @TestTable + ' from ' + @TableToCheck
exec (@sql)
go

--********************************************************************************
-- Run new process here.
-- Do not rerun the test table population unless you can recreate the initial data
--********************************************************************************
if object_id('s_RegressionTest_CompareData', 'P') is not null
	drop proc s_RegressionTest_CompareData
go
create proc s_RegressionTest_CompareData
@TableToCheck varchar(200) ,
@ColToIgnore varchar(200) ,
@CompareOnPrimaryKey varchar(1) ,
@debug int = 0
as
/*
exec s_RegressionTest_CompareData 'MyTable', 'MyTable_Identity' ,'N' ,1
*/

declare @TestTable varchar(210)
select @TestTable = @TableToCheck + '_z_test'
declare @sql varchar(max)

declare @crlf varchar(2)
select @crlf = char(13) + char(10)

-- create table join
-- get unique index columns
declare @IndexType varchar(1000) = 'is_unique = 1'
if @CompareOnPrimaryKey = 'Y'
	select @IndexType = 'is_primary_key = 1 and ' + @IndexType


declare @pk table (colname varchar(200), seq int identity)
select @sql = 'with cte as 
(select top 1 * from sys.indexes i where object_id(''' + @TableToCheck + ''') = i.object_id and ' + @IndexType + ' order by is_primary_key, index_id)
select c.name 
from cte i
join sys.index_columns ic
on i.object_id = ic.object_id
and i.index_id = ic.index_id
join sys.columns c
on ic.object_id = c.object_id
and ic.column_id = c.column_id'

insert @pk exec (@sql)
if @debug = 1
	select * from @pk

if not exists (select * from @pk)
begin
	select 'no unique constraint'
end

-- create join
declare @join varchar(max)
select @join = coalesce(@join + 'and ','on ') + 't1.' + colname + ' = t2.' + colname + @crlf
from @pk

select @join = 'from ' + @TableToCheck + ' t1' + @crlf + 'full outer join ' + @TestTable + ' t2 ' + @crlf + @join
if @debug = 1
	select @join

-- Compare each column of each row
declare @where varchar(max)
declare @cols table (colname varchar(200), seq int identity)
select @sql = 'select COLUMN_NAME from information_schema.columns where TABLE_NAME = ''' + @TableToCheck + ''' and COLUMN_NAME not like ''z_%''' + ' and COLUMN_NAME <> ''' + @ColToIgnore + ''''
if @debug = 1
	select @sql
insert @cols (colname) exec (@sql)
delete @cols where colname in (select colname from @pk)

select @where = null
select @where = coalesce(@where + ' or ','where ') + '(t1.' + colname + ' <> t2.' + colname + ' or (t1.' + colname + ' is null and t2.' + colname + ' is not null' + ')'  + ' or (t1.' + colname + ' is not null and t2.' + colname + ' is null' + ')' + ' )' + @crlf
from @cols
if @debug = 1
	select sqlwhere = @where

select @sql = 'select *'  + @crlf + 'from ' + @TableToCheck + ' t1' + @crlf + ' full outer join ' + @TestTable + ' t2' + @crlf + @sql

declare @select_pklist varchar(max)
declare @select_pkwhere varchar(max)

select	@select_pkwhere = coalesce(@select_pkwhere + ' +'' and ''+ ', 'pk=') + '''' + colname + '='''''' + convert(varchar(100),coalesce(t1.' + colname + ', t2.' + colname + '),126) + '''''''''
		, @select_pklist = coalesce(@select_pklist + ',', '') + colname
from @pk

select @select_pkwhere = 'select top 100 ' + @select_pkwhere

if @debug = 1
	select @select_pkwhere
if @debug = 1
	select @select_pklist

declare @select varchar(max)

select @select = coalesce(@select + ',', '') + colname
from @pk

--select @select = 'select top 100 ' + @select
--if @debug = 1
--	select selectdata = @select

select @sql =  @select_pkwhere + @crlf + @join + @crlf + @where
if @debug = 1
	select selectdata = @sql

declare @PKList table (s varchar(2000))
insert @PKList exec (@sql)
if @debug = 1
	select * from @PKList

select @sql = null
select @sql = coalesce (@sql + @crlf + 'union all ','') + @crlf +'select tbl = ''' + @TableToCheck + ''',* from ' + @TableToCheck + ' where ' + s
from @PKList
select @sql = coalesce (@sql + @crlf + 'union all ','') + @crlf +'select tbl = ''' + @TestTable + ''',* from ' + @TestTable + ' where ' + s
from @PKList
select @sql = @sql + @crlf + ' order by ' + @select_pklist + ',tbl'

if @debug = 1
	select @sql

exec (@sql)
go