SQL_SP:COMPARE WITHIN TWO TABLES

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