MSSQL - Compare Tables

--EXEC USP_CompareTables 'NEWTABLE','OLDTABLE','',''

--DROP PROCEDURE USP_CompareTables

ALTER PROCEDURE USP_CompareTables(

@Table1 varchar(500),                   -- name of table 1

@Table2 Varchar(500),                   -- name of table 2

@ColumnList varchar(MAX) = '',          -- list of columns separated by comma to be compared

@AllowableDifference varchar(10) = '')  -- enter tolerable difference wen it is type real

AS

--DECLARE @Table1 VARCHAR(500); SELECT @Table1='DW_STAGING.lp.Fact_Equipment';DECLARE @Table2 VARCHAR(500); SELECT @Table2='DATAWAREHOUSE.lp.Fact_Equipment';DECLARE @ColumnList VARCHAR(100); SELECT @ColumnList='';DECLARE @AllowableDifference AS VARCHAR(10);SELECT @AllowableDifference='0.002';

DECLARE @UniqueKeyCol VARCHAR(100), @ColumnName VARCHAR(100), @DataType VARCHAR(100), @ColumnComparison VARCHAR(MAX)

-- get primary key column name

SELECT @UniqueKeyCol = Column_Name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE CHARINDEX('.'+TABLE_NAME+' ', @Table1+' ') > 0

IF @@ROWCOUNT <> 0

BEGIN

    -- Get the column comparison sql

    DECLARE ColumnCursor CURSOR FOR

    SELECT c.name ColumnName, t.name DataType

    FROM sys.columns c JOIN sys.types t ON c.user_type_id=t.user_type_id

    WHERE CHARINDEX('.'+OBJECT_NAME(c.OBJECT_ID)+' ', @Table1+' ') > 0 AND c.name <> @UniqueKeyCol 

      AND (@ColumnList = '' OR (@ColumnList <> '' AND CHARINDEX(c.name+',', REPLACE(@ColumnList, ' ', '')+',')>0))

   

    SET @ColumnComparison = ''

    OPEN ColumnCursor FETCH NEXT FROM ColumnCursor INTO @ColumnName, @DataType

   

       WHILE @@FETCH_STATUS = 0

       BEGIN

           IF @ColumnComparison <> ''

               SET @ColumnComparison = @ColumnComparison + ' OR '

          

              SET @ColumnComparison = @ColumnComparison +

              CASE

                     WHEN CHARINDEX(@DataType, 'REAL DECIMAL FLOAT MONEY NUMERIC') > 0 THEN

                     CASE

                     WHEN @AllowableDifference = '' THEN

                            'isnull(t1.' + @ColumnName + ', 0.0)<>isnull(t2.' + @ColumnName + ', 0.0)'

                     ELSE   

                            'ABS(t1.' + @ColumnName + '-t2.' + @ColumnName+')>'+@AllowableDifference

                     END

                     WHEN CHARINDEX(@DataType, 'INT') > 0 THEN

                           'isnull(t1.' + @ColumnName + ', 0)<>isnull(t2.' + @ColumnName + ', 0)'

                     ELSE

                           'isnull(t1.' + @ColumnName + ', '''')<>isnull(t2.' + @ColumnName + ', '''')'

           END

              

           FETCH NEXT FROM ColumnCursor INTO @ColumnName, @DataType

    END

   

    CLOSE ColumnCursor

    DEALLOCATE ColumnCursor

END

ELSE

    SELECT 'No primary key defined in the table: '+@Table1

DECLARE @SQL VARCHAR(MAX);

SET @SQL = 'SELECT DISTINCT t1.' + @UniqueKeyCol + ',t2.' + @UniqueKeyCol + ' FROM ' + @Table1 + ' t1 FULL OUTER JOIN ' + @Table2 + ' t2 ON t1.' + @UniqueKeyCol + '=t2.' + @UniqueKeyCol + ' WHERE (t1.' + @UniqueKeyCol + ' is null AND t2.' + @UniqueKeyCol + ' is not null) OR (t1.' + @UniqueKeyCol + ' is not null AND t2.' + @UniqueKeyCol + ' is null) OR ' + @ColumnComparison + ' ORDER BY 1'

EXEC (@SQL)