Stone Coast Web Design's Weblog

I picked this up from SQLTeam.com and it's come in handy a few times.

CREATE PROCEDURE CompareTables(

@table1 varchar(100), 
@table2 Varchar(100),
@T1ColumnList varchar(1000),
@T2ColumnList varchar(1000) = '')
AS

-- Table1, Table2 are the tables or views to compare.
-- T1ColumnList is the list of columns to compare, from table1.
-- Just list them comma-separated, like in a GROUP BY clause.
-- If T2ColumnList is not specified, it is assumed to be the same
-- as T1ColumnList. Otherwise, list the columns of Table2 in
-- the same order as the columns in table1 that you wish to compare.
--
-- The result is all records from either table that do NOT match
-- the other table, along with which table the record is from.

declare @SQL varchar(8000);

IF @t2ColumnList = '' SET @T2ColumnList = @T1ColumnList

set @SQL = 'SELECT ''' + @table1 + ''' AS TableName, ' + @t1ColumnList +
' FROM ' + @Table1 + ' UNION ALL SELECT ''' + @table2 + ''' As TableName, ' +
@t2ColumnList + ' FROM ' + @Table2

set @SQL = 'SELECT Max(TableName) as TableName, ' + @t1ColumnList +
' FROM (' + @SQL + ') A GROUP BY ' + @t1ColumnList +
' HAVING COUNT(*) = 1'

exec ( @SQL)