Compare two SQL Server tables with stored procedures

• Preethiviraj Kulasingham
• Published: 11 Feb 2005

If you need to compare the data in two tables -- either from the same database or from different databases or even from different servers -- this procedure can help. It checks:

1. The structure of the tables. If both are the same (except minor differencs like check constraints, defaults, etc.) it will proceed
2. Whether the rows are identitical. If not so, it will display a summary of the differences: the number of identitical rows, modified rows (primary key is identitical but other fields are different), new rows, etc.
3. It will display the actiual rows that are different.

It accepts four parameters:

• 1 and 2: the table names. The server name, database name & schema name could be included if needed.
• 3: show differences. If set to 1, the procedure may return the different columns in the structure and the different rows in the data. Otherwise, only the summary is displayed.
• 4: structure only. If set to 1, it will check for the structure differences only.

This SP works with SQL Server 2000 (SP2) and 7. I got the idea through another tip written by iecdba at www.SQLServerCentral.com (Script id 452).

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

-- sp_Compare2Tables            
--                       
-- The SP compares the structure & data in 2 tables.             
-- Tables can 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           
--                      
-- 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)          
--------------------------------------------------------------------------------------------------------------
--                      
--       Created by G.R.Preethiviraj Kulasingham B.Sc., MCP      
--         Written on  : August 29, 2002         
--        Modified on: September 05, 2002         
--                      
--------------------------------------------------------------------------------------------------------------

CREATE PROC sp_Compare2Tables
@TableName1 sysName ,
@TableName2 sysName ,
@ListDiff bit = 1 ,
@StructureOnly bit =0

AS

SET NOCOUNT ON
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON

declare @SQLStr nvarchar(4000), @OrderBy varchar(4000), @ConditionList nvarchar(4000), @FieldList nvarchar(4000)
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+''''
EXECUTE sp_ExecuteSQL @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+''''
EXECUTE sp_ExecuteSQL @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+''''
EXECUTE sp_ExecuteSQL @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+''''
EXECUTE sp_ExecuteSQL @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+''''
EXECUTE sp_ExecuteSQL @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+''''
EXECUTE sp_ExecuteSQL @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(35), GetDate(),109)
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
 
)    
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
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+''''
EXECUTE sp_ExecuteSQL @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
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+''''

EXECUTE sp_ExecuteSQL @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(35), GetDate(),109)
 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=''

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,
  @ConditionList=@ConditionList +' AND (T.'+COLUMN_NAME+ ' = A.'+COLUMN_NAME+' OR (T.'+COLUMN_NAME+' IS NULL AND A.'+COLUMN_NAME+' IS NULL))'
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, 3, LEN(@FieldList)-2)
SET @ConditionList= SUBSTRING(@ConditionList, 5, LEN(@ConditionList)-4)

-- 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
'
EXECUTE sp_ExecuteSQL @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 ='''+@Table1+''')

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
  )'
EXECUTE sp_ExecuteSQL @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(35), GetDate(),109)
 RETURN
END

PRINT '
Both Tables are having 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(35), GetDate(),109)
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   -- 
------------------------------------------------------------------------------------------

set @SQLStr='Select '''+@TableName2+''' TABLE_NAME, '+@FieldList +' 
FROM ['+@SvrName2+'].['+@DBName2+'].['+@Schema2+'].['+@Table2+'] T, (
 Select '+ @FieldList+', SUM(Counter) as Instances FROM 
  (Select '+@FieldList+ ', 1 as Counter FROM ['+@SvrName1+'].['+@DBName1+'].['+@Schema1+'].['+@Table1+'] T 
  UNION ALL
  Select '+ @FieldList+', 1 FROM ['+@SvrName2+'].['+@DBName2+'].['+@Schema2+'].['+@Table2+'] T 
  ) T 
 Group By '+ @FieldList + ' Having SUM(Counter)<2 
 )a  
WHERE '+@ConditionList +'
UNION ALL 
Select '''+@TableName1+''' TABLE_NAME, '+@FieldList +' 
FROM ['+@SvrName1+'].['+@DBName1+'].['+@Schema1+'].['+@Table1+'] T, (
 Select '+ @FieldList+', SUM(Counter) as Instances FROM 
  (Select '+@FieldList+ ', 1 as Counter FROM ['+@SvrName1+'].['+@DBName1+'].['+@Schema1+'].['+@Table1+'] T 
  UNION ALL
  Select '+ @FieldList +', 1 FROM ['+@SvrName2+'].['+@DBName2+'].['+@Schema2+'].['+@Table2+'] T 
  ) T 
 Group By '+ @FieldList + ' Having SUM(Counter)<2 
 ) A  
WHERE '+@ConditionList+'
Order By '+ @OrderBy
EXECUTE sp_ExecuteSQL @SQLStr 

DROP TABLE #Table_Index

DROP TABLE  #ROWCount_Table 
DROP TABLE  #TableColumns
PRINT 'Comparing the data step 2 completed at '+Convert(varchar(35), GetDate(),109)

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free SQL Server tips and scripts.
• Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
• Best Web Links: SQL Server tips, tutorials, and scripts from around the Web.