CREATE procedure sp_Compare ( @TableA varchar(50), @TableB varchar(50), @ExcludeFields varchar(255), @JoinFields varchar(255), @CompType int = 0, @debug bit = 0 )
as
begin
--Comments: This sp can compare two tables with the same schema
-- and determine which records are different
--Usage: The following example assumes you have created this sp in Pubs and have created a Titles2 table similar to Titles and changed some data
-- exec sp_Compare 'Titles','Titles2','Notes,PubDate','Title_Id'
--CompType 1 = Records that exist in A but not in B
-- 2 = Records that exist in B but not in A
-- 3 = Records that exist in both A and B but one or more columns compared have different value
-- 4 = Records that exist in both A and B but all columns compared have same value
-- 0 = Do all 4 comparisons
declare @SQL varchar(8000), @select1 varchar(4000), @from1 varchar(4000), @where1 varchar(4000), @select2 varchar(4000), @from2 varchar(4000), @where2 varchar(4000), @select3 varchar(4000), @from3 varchar(4000), @where3 varchar(4000), @select4 varchar(4000), @from4 varchar(4000), @where4 varchar(4000), @ColName varchar(50), @field varchar(50), @len int, @start int
-- Parse Exclude Fields and populate a temp table
create table #ExcludeFields(field varchar(50))
select @start = 1
select @len = Charindex(',',@ExcludeFields,@start)-1
while @len<> -1
begin
select @field = substring(@ExcludeFields,@start,@len)
insert into #ExcludeFields(field) values ('['+@field+']')
select @ExcludeFields = substring(@ExcludeFields,@len + 2,len(@ExcludeFields))
select @len = Charindex(',',@ExcludeFields,@start)-1
end
insert into #ExcludeFields(field) values ('['+@Excludefields+']')
--Parse join fields and populate a temp table
create table #JoinFields(field varchar(50))
select @start = 1
select @len = Charindex(',',@JoinFields,@start)-1
while @len<> -1
begin
select @field = substring(@JoinFields,@start,@len)
insert into #JoinFields(field) values ('['+@field+']')
select @JoinFields = substring(@JoinFields,@len + 2,len(@JoinFields))
select @len = Charindex(',',@JoinFields,@start)-1
end
insert into #JoinFields(field) values ('['+@JoinFields+']')
--Get a list of columns name in a temp table
select ColName = '[' + rtrim(sc.Name) + ']'
into #Columns from syscolumns sc inner join sysobjects so on sc.id = so.id where so.Name = @TableA order by colid
--Initialize select from and where clause
set @select1 = 'select '
set @select2 = 'select '
set @select3 = 'select '
set @select4 = 'select '
set @from1 = 'from ' + @TableA + ' A left outer join ' + @TableB + ' B on '
set @from2 = 'from ' + @TableA + ' A right outer join ' + @TableB + ' B on '
set @from3 = 'from ' + @TableA + ' A inner join ' + @TableB + ' B on '
set @from4 = 'from ' + @TableA + ' A inner join ' + @TableB + ' B on '
set @where1 = 'where ' set @where2 = 'where ' set @where3 = 'where '
set @where4 = 'where '
--Build select from and where clauses
declare cBuildSQL cursor
for select ColName from #Columns
open cBuildSQL
fetch next from cBuildSQL into @ColName
while @@fetch_status = 0
begin
--select clauses
set @ColName = left(@ColName,len(@ColName)-1)
--select 1
select @select1 = @select1 + @ColName + '_A] = A.' + @ColName + '], '
--select 2
select @select2 = @select2 + @ColName + '_B] = B.' + @ColName + '], '
--select 3
select @select3 = @select3 + @ColName + '_A] = A.' + @ColName + '], '
select @select3 = @select3 + @ColName + '_B] = B.' + @ColName + '], '
--select 4
select @select4 = @select4 + @ColName + '_A] = A.' + @ColName + '], '
select @select4 = @select4 + @ColName + '_B] = B.' + @ColName + '], '
set @ColName = @ColName + ']'
--from clause - Only include the fields in the Join list
if @ColName in(select field from #JoinFields)
begin
select @from1 = @from1 + 'A.' + @ColName + ' = B.' + @ColName + ' and '
select @from2 = @from2 + 'A.' + @ColName + ' = B.' + @ColName + ' and '
select @from3 = @from3 + 'A.' + @ColName + ' = B.' + @ColName + ' and '
select @from4 = @from4 + 'A.' + @ColName + ' = B.' + @ColName + ' and ' end
--where clause - Do not include the fields in the exclusion list
if @ColName not in(select field from #ExcludeFields)
begin
if @ColName in(select field from #JoinFields)
begin
select @where1 = @where1 + 'B.' + @ColName + ' is null and '
select @where2 = @where2 + 'A.' + @ColName + ' is null and '
end
else
begin
select @where3 = @where3 + 'A.' + @ColName + ' <> ' + 'B.' + @ColName + ' or '
select @where4 = @where4 + 'A.' + @ColName + ' = ' + 'B.' + @ColName + ' and '
end
end
fetch next from cBuildSQL into @ColName
end
deallocate cBuildSQL
--get rid of trailing ',' from select clause, 'and' from from clause and 'or' from where clause
set @select1 = left(@select1,len(@select1)-1)
set @select2 = left(@select2,len(@select2)-1)
set @select3 = left(@select3,len(@select3)-1)
set @select4 = left(@select4,len(@select4)-1)
set @from1 = left(@from1,len(@from1)-4)
set @from2 = left(@from2,len(@from2)-4)
set @from3 = left(@from3,len(@from3)-4)
set @from4 = left(@from4,len(@from4)-4)
set @where1 = left(@where1,len(@where1) - 4)
set @where2 = left(@where2,len(@where2) - 4)
set @where3 = left(@where3,len(@where3) - 3)
set @where4 = left(@where4,len(@where4) - 4)
if @debug = 1
begin
select field from #ExcludeFields
select field from #JoinFields
select select1 = @select1
select select2 = @select2
select select3 = @select3
select select4 = @select4
select from1 = @from1
select from2 = @from2
select from3 = @from3
select from4 = @from4
select where1 = @where1
select where2 = @where2
select where3 = @where3
select where4 = @where4
end
if @CompType = 0
begin
select Message = 'The following are the records that exist in table A but not in table B'
exec (@select1 + ' ' + @from1 + ' ' + @where1)
select Message = 'The following are the records that exist in table B but not in table A'
exec (@select2 + ' ' + @from2 + ' ' + @where2)
select Message = 'The following are the records that exist in both table A and table B but one or more columns compared are different'
exec (@select3 + ' ' + @from3 + ' ' + @where3)
select Message = 'The following are the records that exist in both table A and table B and all the columns compared are same'
exec (@select4 + ' ' + @from4 + ' ' + @where4)
end
if @CompType = 1
begin
select Message = 'The following are the records that exist in table A but not in table B'
exec (@select1 + ' ' + @from1 + ' ' + @where1)
end
if @CompType = 2
begin
select Message = 'The following are the records that exist in table B but not in table A'
exec (@select2 + ' ' + @from2 + ' ' + @where2)
end
if @CompType = 3
begin
select Message = 'The following are the records that exist in both table A and table B but one or more columns compared are different'
exec (@select3 + ' ' + @from3 + ' ' + @where3)
end
if @CompType = 4
begin
select Message = 'The following are the records that exist in both table A and table B and all the columns compared are same'
exec (@select4 + ' ' + @from4 + ' ' + @where4)
end
drop table #columns,#ExcludeFields,#JoinFieldsend
GO