Compare the Data of 2 Tables (Improved)

IF Exists(Select id from sysobjects where id = object_id('sp_CompareTwoTables') and type ='P')
Drop Procedure sp_Compare2Tables
GO

-- sp_CompareTwoTables
-- 
-- The SP compares the structure & data in 2 tables. 
-- Tables could be from different servers, different databases or different schemas. 
--------------------------------------------------------------------------------------------------------------
-- Parameters:
-- 1. @Table1 - Name of the table to be checked.
-- 2. @Table2 - Name of the table to be checked.
-- 3. @ListDiff - Bit to list the differences
-- 4. @StructureOnly - Bit to compare only the structure
-- 5. @CheckNulls - Bit to skip the NULL checks on columns
--
-- Assumptions: The length of the dynamic string should not exceed 4000 characters
--Both tables have primary keys
--Primary key combination is same for both tables
-- Paramenter 1, 2: Table name (With optional server name, database name, Schema name seperated with .)
-- Eg. Preethi.Inventory.Dbo.TranHeader, Preethi.Test.dbo.Tran
--     Any of the first 3 parts could be omitted.
--     Inventory.DBO.TranHeader, INV.TranHeader and TranHeader are valid
-- Parameter 3:List the differences 
--IF True it will list all the different fields (in case of structural difference)
--or all the different entries (in case of data differences)
--Default is 1 (List the differences)
-- Parameter 4:Compare only the structure
--Default=0 (Compare data & structure)
-- Parameter 5:Skip the 'or (table1.column is NULL and table2.column is NULL)'
--                    checks for tables with many columns or no NULL values
--                  Default =1 (check for NULL values)
--------------------------------------------------------------------------------------------------------------
--
-- Created by G.R.Preethiviraj Kulasingham B.Sc., MCP
-- Written on  : August 29, 2002
--Modified on: September 05, 2002
--
--------------------------------------------------------------------------------------------------------------
/*
    MODIFICATIONS: Made by Boyd Staley 11/2002

    1-Fixed Bug that reported tables as identical if table 2 just had extra rows
    2-Changed variables from nvarchar(4000) to varchar(8000) to accomodate tables
        with more columns
    3-Removed etraneous spaces from Dynamic Sql Statements to accomodate tables
        with more columns
    4-Added check to see if column is nullable to skip the NULL checks to accomdate
        tables with more columns
    5-Added @CheckNulls parameter to skip NULL checks if desired to accomodate tables
        with more column
    6-Broke up the 'Row Listing' query into sevaral individual queries to accomodate
        tables with more columns 
    NOTE: This required the use of GLOBAL temporary tables so be aware of this



*/CREATE PROC sp_CompareTwoTables
@TableName1 sysName ,
@TableName2 sysName ,
@ListDiff bit = 1 ,
@StructureOnly bit =0 ,
@CheckNulls bit = 1

AS

SET NOCOUNT ON
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON

declare @SQLStr varchar(8000), @OrderBy varchar(8000), @ConditionList varchar(8000), 
@FieldList varchar(8000),@ColumnInfo varchar(8000)
declare @SvrName1 sysname, @DBName1 sysname, @Schema1 Sysname, @Table1 Sysname 
Declare @SvrName2 sysname, @DBName2 sysname, @Schema2 sysname, @Table2 sysname
declare @Int1 int, @Int2 int, @Int3 int, @Int4 int 
--Declare @TimeStamp bit


set @TableName1= RTRIM(LTRIM(@TableName1))
set @Table1 = @TableName1
set @SvrName1 = @@SERVERNAME
Set @DBName1 = DB_NAME()
set @Schema1 = CURRENT_USER

set @TableName2= RTRIM(LTRIM(@TableName2))
set @Table2 = @TableName2
set @SvrName2 = @@SERVERNAME
Set @DBName2 = DB_NAME()
set @Schema2 = CURRENT_USER

-- Check for the existance of specified Servers, databases, schemas and tables
IF CHARINDEX('.',@Table1) > 0
BEGIN
set @Schema1 = LEFT(@Table1,CHARINDEX('.',@Table1)-1)
set @Table1 = RIGHT(@Table1,LEN(@Table1)-CHARINDEX('.',@Table1))
IF CHARINDEX('.',@Table1) > 0
BEGIN
Set @DBName1=@Schema1
set @Schema1 = LEFT(@Table1,CHARINDEX('.',@Table1)-1)
set @Table1 = RIGHT(@Table1,LEN(@Table1)-CHARINDEX('.',@Table1))
IF CHARINDEX('.',@Table1) > 0
BEGIN
SET @SvrName1=@DBName1
Set @DBName1=@Schema1
set @Schema1 = LEFT(@Table1,CHARINDEX('.',@Table1)-1)
set @Table1 = RIGHT(@Table1,LEN(@Table1)-CHARINDEX('.',@Table1))
END
END
END
IF LEFT(@SvrName1,1)='['  AND RIGHT(@SvrName1,1)=']' 
SET @SvrName1=SUBSTRING(@SvrName1, 2, LEN(@SvrName1)-2)
IF LEFT(@DBName1,1)='[' AND RIGHT(@DBName1,1)=']' 
SET @DBName1=SUBSTRING(@DBName1, 2, LEN(@DBName1)-2)
IF LEFT(@Schema1,1)='['  AND RIGHT(@Schema1,1)=']' 
SET @Schema1=SUBSTRING(@Schema1, 2, LEN(@Schema1)-2)
IF LEFT(@Table1,1)='[' AND RIGHT(@Table1,1)=']' 
SET @Table1=SUBSTRING(@Table1, 2, LEN(@Table1)-2)


IF @SvrName1<>@@SERVERNAME
IF not exists (select * FROM master.dbo.sysservers where srvname = @SvrName1)
BEGIN
PRINT 'There is no linked server named '+@SvrName1+'. Termination of Procedure.'
RETURN 
END

set @SQLStr = 'Select name into #TempTable FROM ['+@SvrName1+'].master.dbo.sysdatabases where name ='''+ @DBName1+''''
exec (@SQLstr)
IF @@rowcount=0
BEGIN
PRINT 'There is no database named '+@DBName1+'. Termination of Procedure.'
RETURN 
END

set @SQLStr = 'Select name INTO #TempTable FROM ['+@SvrName1+'].['+@DBName1+'].dbo.sysusers where name ='''+ @Schema1+''''
exec (@SQLstr)
IF @@rowcount=0
BEGIN
PRINT 'There is no schema named '+@Schema1+' in the specified Database. Termination of Procedure.'
RETURN 
END

set @SQLStr = 'Select o.name into #TempTable FROM  ['+@SvrName1+'].['+@DBName1+'].dbo.sysobjects O, ['+@SvrName1+'].['+@DBName1+'].dbo.sysusers U Where O.uid=U.Uid and U.Name =''' + @Schema1 +''' and O.name=''' +@Table1+''''
exec (@SQLstr)
IF @@rowcount = 0
BEGIN
PRINT 'There is no Table named '+@Table1+'. END of work.'
RETURN 
END


IF CHARINDEX('.',@Table2) > 0
BEGIN
set @Schema2 = LEFT(@Table2,CHARINDEX('.',@Table2)-1)
set @Table2 = RIGHT(@Table2,LEN(@Table2)-CHARINDEX('.',@Table2))
IF CHARINDEX('.',@Table2) > 0
BEGIN
Set @DBName2=@Schema2
set @Schema2 = LEFT(@Table2,CHARINDEX('.',@Table2)-1)
set @Table2 = RIGHT(@Table2,LEN(@Table2)-CHARINDEX('.',@Table2))
IF CHARINDEX('.',@Table2) > 0
BEGIN
SET @SvrName2=@DBName2
Set @DBName2=@Schema2
set @Schema2 = LEFT(@Table2,CHARINDEX('.',@Table2)-1)
set @Table2 = RIGHT(@Table2,LEN(@Table2)-CHARINDEX('.',@Table2))
END
END
END

IF LEFT(@SvrName2,1)='['  AND RIGHT(@SvrName2,1)=']' 
SET @SvrName2=SUBSTRING(@SvrName2, 2, LEN(@SvrName2)-2)
IF LEFT(@DBName2,1)='[' AND RIGHT(@DBName2,1)=']' 
SET @DBName2=SUBSTRING(@DBName2, 2, LEN(@DBName2)-2)
IF LEFT(@Schema2,1)='['  AND RIGHT(@Schema2,1)=']' 
SET @Schema2=SUBSTRING(@Schema2, 2, LEN(@Schema2)-2)
IF LEFT(@Table2,1)='[' AND RIGHT(@Table2,1)=']' 
SET @Table2=SUBSTRING(@Table2, 2, LEN(@Table2)-2)

IF @SvrName2<>@@SERVERNAME
IF not exists (select * FROM master.dbo.sysservers where srvname = @SvrName2)
BEGIN
PRINT 'There is no linked server named '+@SvrName2+'. Termination of Procedure.'
RETURN 
END

set @SQLStr = 'Select name into #TempTable FROM ['+@SvrName2+'].master.dbo.sysdatabases where name ='''+ @DBName2+''''
exec (@SQLstr)
IF @@rowcount=0
BEGIN
PRINT 'There is no database named '+@DBName2+'. Termination of Procedure.'
RETURN 
END

set @SQLStr = 'Select name into #TempTable FROM ['+@SvrName2+'].['+@DBName2+'].dbo.sysusers where name ='''+ @Schema2+''''
exec (@SQLstr)
IF @@rowcount=0
BEGIN
PRINT 'There is no schema named '+@Schema2+'in the specIFied Database. Termination of Procedure.'
RETURN 
END

set @SQLStr = 'Select o.name into #TempTable FROM  ['+@SvrName2+'].['+@DBName2+'].dbo.sysobjects O, ['+@SvrName2+'].['+@DBName2+'].dbo.sysusers U Where O.uid=U.Uid and U.Name =''' + @Schema2 +''' and O.name=''' +@Table2+''''
exec (@SQLstr)
IF @@rowcount = 0
BEGIN
PRINT 'There is no Table named '+@Table2+'. END of work.'
RETURN 
END

-- Check whether both tables are same.
IF (@SvrName1 + @DbName1 + @Schema1 + @Table1)=(@SvrName2 + @DbName2 + @Schema2 + @Table2)
BEGIN
PRINT 'Both Tables  should be different. Termination of Procedure'
RETURN
END
-- Check whether the structure of both tables are same.
-- Method: Get the tables with column data 
--Select the no of rows in each and in union.
--If both are same they are same
Print 'Comparing the structure started at '+Convert(varchar(20), GetDate(),114)
Create Table #TableColumns 
(    
TABLE_SERVER sysname NOT NULL,    
TABLE_CATALOG sysname NOT NULL,    
TABLE_SCHEMA sysname NOT NULL,    
TABLE_NAME sysname NOT NULL,    
COLUMN_NAME sysname NOT NULL,    
ORDINAL_POSITION smallint NOT NULL,    
DATA_TYPE sysname NOT NULL,    
CHARACTER_MAXIMUM_LENGTH int NULL,    
CHARACTER_OCTET_LENGTH int NULL,    
NUMERIC_PRECISION tinyint NULL,    
NUMERIC_PRECISION_RADIX smallint NULL,    
NUMERIC_SCALE int NULL,     
DATETIME_PRECISION smallint NULL
    ,ISNULLABLE int NULL

)    

Create Table #Table_Index  
(
ColumnName sysname NOT NULL,
OrderID  Int NOT NULL
)
 
Create Table #ROWCount_Table
(
Int1 int NOT NULL, 
Int2 int NULL, 
Int3 int NULL,
Int4 int NULL
)



set @SQLStr = 'Insert into  #TableColumns 
SELECT '''+@SvrName1+''', '''+@DBName1 +''',    
usr.name, obj.name,    
Col.name,    
col.colid,    
spt_dtp.LOCAL_TYPE_NAME,    
convert(int, OdbcPrec(col.xtype, col.length, col.xprec)  + spt_dtp.charbin),    
convert(int, spt_dtp.charbin + 
   case when spt_dtp.LOCAL_TYPE_NAME in (''nchar'', ''nvarchar'', ''ntext'')
 then  2*OdbcPrec(col.xtype, col.length, col.xprec) 
 else  OdbcPrec(col.xtype, col.length, col.xprec) 
   end),    
nullif(col.xprec, 0),    
spt_dtp.RADIX,    
col.scale,    
spt_dtp.SQL_DATETIME_SUB
    ,col.ISNULLABLE
FROM ['+@SvrName1+'].['+@DBName1+'].dbo.sysobjects obj,
['+@SvrName1+'].master.dbo.spt_datatype_info spt_dtp,
['+@SvrName1+'].['+@DBName1 +'].dbo.systypes typ,
['+@SvrName1+'].['+@DBName1 +'].dbo.sysusers usr,
['+@SvrName1+'].['+@DBName1 +'].dbo.syscolumns col     
WHERE
obj.id = col.id
     AND obj.uid=usr.uid 
AND typ.xtype = spt_dtp.ss_dtype
AND (spt_dtp.ODBCVer is null or spt_dtp.ODBCVer = 2)
AND obj.xtype in (''U'', ''V'')
AND col.xusertype = typ.xusertype
AND (spt_dtp.AUTO_INCREMENT is null or spt_dtp.AUTO_INCREMENT = 0) 
AND obj.name =''' + @Table1+ ''' and usr.name ='''+@Schema1+''''

exec (@SQLstr) 

set @SQLStr = 'Insert into  #TableColumns 
SELECT '''+@SvrName2+''', '''+@DbName2 +''',    
usr.name, obj.name,    
Col.name,    
col.colid,    
spt_dtp.LOCAL_TYPE_NAME,    
convert(int, OdbcPrec(col.xtype, col.length, col.xprec)  + spt_dtp.charbin),    
convert(int, spt_dtp.charbin + 
   case when spt_dtp.LOCAL_TYPE_NAME in (''nchar'', ''nvarchar'', ''ntext'')
 then  2*OdbcPrec(col.xtype, col.length, col.xprec) 
 else  OdbcPrec(col.xtype, col.length, col.xprec) 
   end),    
nullif(col.xprec, 0),    
spt_dtp.RADIX,    
col.scale,    
spt_dtp.SQL_DATETIME_SUB
    ,col.ISNULLABLE
FROM ['+@SvrName2+'].['+@DBName2+'].dbo.sysobjects obj,
['+@SvrName2+'].master.dbo.spt_datatype_info spt_dtp,
['+@SvrName2+'].['+@DBName2 +'].dbo.systypes typ,
['+@SvrName2+'].['+@DBName2 +'].dbo.sysusers usr,
['+@SvrName2+'].['+@DBName2 +'].dbo.syscolumns col     
WHERE
obj.id = col.id
     AND obj.uid=usr.uid 
AND typ.xtype = spt_dtp.ss_dtype
AND (spt_dtp.ODBCVer is null or spt_dtp.ODBCVer = 2)
AND obj.xtype in (''U'', ''V'')
AND col.xusertype = typ.xusertype
AND (spt_dtp.AUTO_INCREMENT is null or spt_dtp.AUTO_INCREMENT = 0) 
AND obj.name =''' + @Table2+ ''' and usr.name ='''+@Schema2+''''

exec (@SQLstr) 

IF EXISTS(SELECT COLUMN_NAME, 
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
CHARACTER_OCTET_LENGTH,
NUMERIC_PRECISION,
NUMERIC_PRECISION_RADIX,
NUMERIC_SCALE,
DATETIME_PRECISION,
COUNT(*) AS  NUMBERS 
FROM #TableColumns 
GROUP BY COLUMN_NAME, 
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
CHARACTER_OCTET_LENGTH,
NUMERIC_PRECISION,
NUMERIC_PRECISION_RADIX,
NUMERIC_SCALE,
DATETIME_PRECISION
HAVING COUNT(*)=1)
BEGIN
PRINT 'The Structure of the tables are different. Termination of Procedure.'
IF @ListDiff =1
SELECT A.* 
FROM #TableColumns A, 
(SELECT COLUMN_NAME, 
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
CHARACTER_OCTET_LENGTH,
NUMERIC_PRECISION,
NUMERIC_PRECISION_RADIX,
NUMERIC_SCALE,
DATETIME_PRECISION,
COUNT(*) as NUMBERS 
FROM #TableColumns 
GROUP BY COLUMN_NAME, 
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
CHARACTER_OCTET_LENGTH,
NUMERIC_PRECISION,
NUMERIC_PRECISION_RADIX,
NUMERIC_SCALE,
DATETIME_PRECISION
HAVING COUNT(*) =1) B
WHERE A.COLUMN_NAME = B.COLUMN_NAME AND 
A.DATA_TYPE = B.DATA_TYPE AND
(ISNULL(A.CHARACTER_MAXIMUM_LENGTH,0)=ISNULL(B.CHARACTER_MAXIMUM_LENGTH,0)) AND
(ISNULL(A.NUMERIC_PRECISION, 0)=ISNULL(B.NUMERIC_PRECISION,0)) AND
(ISNULL(A.NUMERIC_PRECISION_RADIX, 0)=ISNULL(B.NUMERIC_PRECISION_RADIX,0)) AND
(ISNULL(A.NUMERIC_SCALE, 0)=ISNULL(B.NUMERIC_SCALE,0)) AND
(ISNULL(A.DATETIME_PRECISION, 0)=ISNULL(B.DATETIME_PRECISION,0)) 
ORDER BY A.ORDINAL_POSITION 

DROP TABLE  #ROWCount_Table 
DROP TABLE  #TableColumns
Print 'Comparing the structure completed at '+Convert(varchar(20), GetDate(),114)
RETURN
END
IF @StructureOnly=1
BEGIN
DROP TABLE  #ROWCount_Table 
DROP TABLE  #TableColumns
RETURN
END


-----------------------------------------------------------------------------------------
-- Check for the presence of timestamp column 
-----------------------------------------------------------------------------------------
-- NOTE: This First Method is a simple method to check Whether Both Tables are Identitical.

Print 'Comparing the data started at '+Convert(varchar(20), GetDate(),114)
SELECT @ConditionList='', @FieldList='', @ColumnInfo=''

--select * from #TableColumns

SET @Int1=0
WHILE Exists(Select * FROM #TableColumns Where ORDINAL_POSITION>@Int1 and DATA_Type<>'TIMESTAMP' and 
TABLE_SERVER = @SvrName1 AND TABLE_CATALOG = @DBName1 and TABLE_Schema =@Schema1 and TABLE_Name= @Table1)
SELECT @FieldList=@FieldList+',T.'+COLUMN_NAME, 
@Int1 = ORDINAL_POSITION,
--NULL comparisons cause the list to be too long on tables with many columns
        @ConditionList = 
        case 
        when isnullable = 1 and @CheckNulls = 1 then
    @ConditionList +'AND(T.'+COLUMN_NAME+ '=A.'+COLUMN_NAME+' OR(T.'+COLUMN_NAME+' IS NULL AND A.'+COLUMN_NAME+' IS NULL))'
        else
    @ConditionList +'AND(T.'+COLUMN_NAME+ '=A.'+COLUMN_NAME+')'
        end
FROM  #TableColumns 
WHERE TABLE_SERVER = @SvrName1 AND 
TABLE_CATALOG = @DBName1 and 
TABLE_Schema =@Schema1 and 
TABLE_Name= @Table1 and
ORDINAL_POSITION =(Select MIN(ORDINAL_POSITION) 
FROM #TableColumns 
WHERE  ORDINAL_POSITION>@Int1 and 
DATA_Type<>'TIMESTAMP' and 
TABLE_SERVER = @SvrName1 and 
TABLE_CATALOG=@DBName1 and 
TABLE_Schema =@Schema1 and 
TABLE_Name= @Table1) 

SET @FieldList= SUBSTRING(@FieldList, 2, LEN(@FieldList)-1)
SET @ConditionList= SUBSTRING(@ConditionList, 4, LEN(@ConditionList)-3)

-- Code to find the primary key should be placed here.
SET @SQLStr='
Insert Into #Table_Index (ColumnName, OrderID)
select C.Name, k.keyno  
from ['+@SvrName1+'].['+@DbName1+'].dbo.sysobjects O, 
['+@SvrName1+'].['+@DbName1+'].dbo.sysindexes I, 
['+@SvrName1+'].['+@DbName1+'].dbo.sysindexkeys K, 
['+@SvrName1+'].['+@DbName1+'].dbo.syscolumns C, 
['+@SvrName1+'].['+@DbName1+'].dbo.sysusers U
where O.uid = u.uid and u.name = '''+@Schema1+''' and O.name ='''+@Table1+''' and I.id = O.id and
I.indid=1 and I.indid = k.indid and O.id = k.id and k.colid =C.Colid and C.id =O.id
'
exec (@SQLstr) 

SET @Int1=0
SET @OrderBy =''
WHILE Exists(Select * from #Table_Index Where OrderID >@Int1)
Select @OrderBy = @OrderBy+', T.'+ColumnName, @Int1=OrderID 
From #Table_Index Where OrderID >@Int1 and OrderID =(
Select Min(OrderID) From #Table_Index where OrderID >@Int1)
IF @OrderBy =''--No Primary Index Found
SET @OrderBy =@FieldList
ELSE
SET @OrderBy= SUBSTRING(@OrderBy, 3, LEN(@OrderBy)-2)




SET @SQLStr='
INSERT INTO #ROWCount_Table Select i.[rows],0,0, 0
FROM ['+@SvrName1+'].['+@DBName1+'].dbo.sysindexes i, 
['+@SvrName1+'].['+@DBName1+'].dbo.sysObjects o, 
['+@SvrName1+'].['+@DBName1+'].dbo.sysusers u
Where o.id=i.id and u.uid = o.uid and i.indid<2 and 
u.name='''+@Schema1+''' and o.name ='''+@Table1+'''
 
update #ROWCount_Table set Int2 =
(
Select i.[rows] 
FROM ['+@SvrName2+'].['+@DBName2+'].dbo.sysindexes i, 
['+@SvrName2+'].['+@DBName2+'].dbo.sysObjects o, 
['+@SvrName2+'].['+@DBName2+'].dbo.sysusers u
Where o.id=i.id and u.uid = o.uid and i.indid<2 and
u.name='''+@Schema1+''' and o.name ='''+@Table2+''')

Update #ROWCount_Table Set Int3=
(
Select Count(1) FROM 
(
Select '+ @FieldList +' 
FROM ['+@SvrName1+'].['+@DBName1+'].['+@Schema1+'].['+@Table1+'] T 
UNION
Select '+ @FieldList +' 
FROM ['+@SvrName2+'].['+@DBName2+'].['+@Schema2+'].['+@Table2+'] T 
) A
)
Update #ROWCount_Table Set Int4=
(
Select Count(1) FROM 
(
Select '+ @OrderBy +' 
FROM ['+@SvrName1+'].['+@DBName1+'].['+@Schema1+'].['+@Table1+'] T 
UNION
Select '+ @OrderBy +' 
FROM ['+@SvrName2+'].['+@DBName2+'].['+@Schema2+'].['+@Table2+'] T 
) A
)'
exec (@SQLstr) 

Select @Int1=Int1, @Int2=Int2, @Int3=Int3, @Int4=Int4 FROM #ROWCount_Table 
IF @Int1=@Int3 and @Int2=@Int3 
BEGIN
PRINT 'Both Tables are identitical.'
DROP TABLE  #ROWCount_Table 
DROP TABLE  #TableColumns
Print 'Comparing the data completed at '+Convert(varchar(20), GetDate(),114)
RETURN
END

PRINT '
The Tables have different data
------------------------------------------------------
No. of records in '+@TableName1+ ' are '+Convert(Varchar(20), @Int1)+'.
No. of records in '+@TableName2+ ' are '+Convert(Varchar(20), @Int2)+'.
No. of records common in both are '+Convert(Varchar(20), @Int1+@int2-@Int3)+'.
No. of unmatched records in '+@TableName1+ ' are '+Convert(Varchar(20),@int3-@Int2)+'.
No. of unmatched records in '+@TableName2+ ' are '+Convert(Varchar(20),@int3-@Int1)+'.

No. of New records in '+@TableName1+ ' are '+Convert(varchar(20), @Int4-@Int2)+'.
No. of New records in '+@TableName2+ ' are '+Convert(varchar(20), @Int4-@Int1)+'.
No. of modified but existing records are '+Convert(varchar(20), @Int3-@Int4)+'.
------------------------------------------------------

Comparing the data step 1 completed at '+Convert(varchar(20), GetDate(),114)
IF @ListDiff = 0
BEGIN
DROP TABLE #Table_Index
DROP TABLE  #ROWCount_Table 
DROP TABLE  #TableColumns
RETURN
END
------------------------------------------------------------------------------------------
--   Now the Tables are not identitical. Now List all the Rows that are different   --
------------------------------------------------------------------------------------------


if ((select object_id('tempdb..##Temp1','U')) is not null )
    drop table ##Temp1
if ((select object_id('tempdb..##Temp2','U')) is not null )
    drop table ##Temp2
if ((select object_id('tempdb..##Temp3','U')) is not null )
    drop table ##Temp3
if ((select object_id('tempdb..##Temp4','U')) is not null )
    drop table ##Temp4

set @SQLSTR='Select '+@FieldList+ ', 1 as Counter  into ##Temp1
           FROM ['+@SvrName1+'].['+@DBName1+'].['+@Schema1+'].['+@Table1+'] T 
   UNION ALL
   Select '+ @FieldList+', 1 
           FROM ['+@SvrName2+'].['+@DBName2+'].['+@Schema2+'].['+@Table2+'] T '
exec (@SQLstr)

set @SQLSTR='
Select '+ @FieldList+', SUM(Counter) as Instances 
      into ##Temp2 FROM 
  (Select * from ##Temp1) T 
      Group By '+ @FieldList + ' Having SUM(Counter)<2 '
exec (@SQLSTR)

set @SQLSTR='
Select '''+@TableName2+''' TABLE_NAME, t.* 
into ##Temp3 FROM ['+@SvrName2+'].['+@DBName2+'].['+@Schema2+'].['+@Table2+'] T
    ,(Select * from ##Temp2
)a  
WHERE '+ @ConditionList

exec (@SQLSTR)

set @SQLSTR='
Select '''+@TableName1+''' TABLE_NAME, t.* 
into ##Temp4 FROM ['+@SvrName1+'].['+@DBName1+'].['+@Schema1+'].['+@Table1+'] T
    ,(Select * from ##Temp2
) A  
WHERE '+ @ConditionList

exec (@SQLSTR)

set @SQLStr='
Select t.* from ##Temp3 t
UNION ALL 
Select t.* from ##Temp4 t
Order by ' + @OrderBy

exec (@SQLstr) 
DROP TABLE #Table_Index

DROP TABLE  #ROWCount_Table 
DROP TABLE  #TableColumns
DROP TABLE ##Temp1
DROP TABLE ##Temp2
DROP TABLE ##Temp3
DROP TABLE ##Temp4
PRINT 'Comparing the data step 2 completed at '+Convert(varchar(20), GetDate(),114)