create procedure sp_Compare (
				@TableA varchar(50),
				@TableB varchar(50),
				@ExcludeFields varchar(255),
				@JoinFields varchar(255),
				@CompType int = 0,
				@debug bit = 0
				)
as
begin
	--Author: 	Subhan Munshi
	--Date:		02/04/2003
	--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

	set nocount on
	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,#JoinFields
end