Compare the Data of 2 Tables (Improved)

,

This is a modification of the excellent procedure by G.R.Preethiviraj Kulasingham B.Sc., MCP    .

The Modifications allow the procedure to successfully list differnces between tables with many more columns than the original.

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)

Rate

Share

Share

Rate