-- Stored Procedure: dbo.CompareTwoTables -- Bill Wunder use admin GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CompareTwoTables]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[CompareTwoTables] GO /* -- log in as sa admin.dbo.addlinkedserver lnkDEV_SQL, DEV_SQL admin.dbo.addlinkedserver lnkA_SQL, A_SQL admin.dbo.addlinkedserver lnkSQL, SQL admin.dbo.addlinkedserver [lnkSQL2], [SQL2] select * from master..sysservers --not so big compareTwoTables @TableName1 = 'lnkSQL.customer.dbo.DAL_DataClass' , @TableName2 = '[lnkSQL2].customer.dbo.DAL_DataClass' compareTwoTables @TableName1 = 'lnkDEV_SQL.customer.dbo.DAL_DataClass' , @TableName2 = 'lnkA_SQL.customer.dbo.DAL_DataClass' , @CompareData = 'True' , @DisplayDataDrift = 'True' -- , @ColumnList = 'DataClass' -- , @IncludeInDataCompare = 'False' , @Filter = 'WHERE DataClass not like ''Metrics%''' -- , @CheckNulls = 'True' -- , @debug = 1 compareTwoTables @TableName1 = 'lnkSQL.customer.dbo.DAL_DataClass' , @TableName2 = '[lnkSQL2].customer.dbo.DAL_DataClass' , @CompareData = 'True' --not so big with more diffs admin.dbo.compareTwoTables @TableName1 = 'lnkSQL.customer.dbo.DAL_OpenSQLConfig' , @TableName2 = '[lnkSQL2].customer.dbo.DAL_OpenSQLConfig' admin.dbo.compareTwoTables @TableName1 = 'lnkSQL.customer.dbo.DAL_OpenSQLConfig' , @TableName2 = '[lnkSQL2].customer.dbo.DAL_OpenSQLConfig' , @CompareData = 'True' admin.dbo.compareTwoTables @TableName1 = 'lnkDEV_SQL.customer.dbo.DAL_OpenSQLConfig' , @TableName2 = 'lnkA_SQL.customer.dbo.DAL_OpenSQLConfig' , @CompareData = 'True' , @DisplayDataDrift = 'True' admin.dbo.compareTwoTables @TableName1 = 'lnkA_SQL.customer.dbo.DAL_OpenSQLConfig' , @TableName2 = '[lnkSQL2].customer.dbo.DAL_OpenSQLConfig' , @CompareData = 'True' , @DisplayDataDrift = 'True' admin.dbo.compareTwoTables @TableName1 = 'lnkSQL.customer.dbo.DAL_OpenSQLConfig' , @TableName2 = '[lnkSQL2].customer.dbo.DAL_OpenSQLConfig' , @CompareData = 'True' , @DisplayDataDrift = 'True' admin.dbo.compareTwoTables @TableName1 = 'lnkSQL.customer.dbo.DAL_OpenSQLConfig' , @TableName2 = '[lnkSQL2].customer.dbo.DAL_OpenSQLConfig' , @CompareData = 'True' , @DisplayDataDrift = 'True' , @ColumnList = 'ReportLabel' , @ColumnList = 'QueryProc,ReportLabel' , @IncludeInDataCompare = 'False' -- , @CheckNulls = 'True' -- , @debug = 1 admin.dbo.compareTwoTables @TableName1 = 'lnkSQL.customer.dbo.DAL_OpenSQLConfig' , @TableName2 = '[lnkSQL2].customer.dbo.DAL_OpenSQLConfig' , @CompareData = 'True' , @DisplayDataDrift = 'True' , @ColumnList = 'QueryProc,ReportLabel' , @IncludeInDataCompare = 'True' , @CheckNulls = 'True' -- , @debug = 1 -- big one compareTwoTables @TableName1 = 'lnkSQL.customer.dbo.port_rollup' , @TableName2 = '[lnkSQL2].customer.dbo.port_rollup' */ CREATE PROC dbo.CompareTwoTables @TableName1 sysName , @TableName2 sysName , @CompareData varchar(5) = 'false', @DisplayDataDrift varchar(5) = 'false', @ColumnList varchar(1024) = '', -- comma delimited list @IncludeInDataCompare char(5) = 'true', -- if false and column list provided will exclude them from data compare @Filter varchar(1024) = '', -- where clause for datacompare, proc prepends WHERE if not specified @CheckNulls varchar(5) = 'true', -- set false if lots-o-columns in table causes the proc to fail @debug bit = 0 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) , @SvrName1 sysname , @DBName1 sysname , @Schema1 Sysname , @Table1 Sysname , @SvrName2 sysname , @DBName2 sysname , @Schema2 sysname , @Table2 sysname , @Int1 int , @Int2 int , @Int3 int , @Int4 int -- , @TimeStamp bit , @CrLf char(2) , @ColumnInList varchar(128) set @CrLf = char(13) + char(10) 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 'Table1 and Table2 cannot be the same table. 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 'Compare of '+ @CrLf + char(9) + @TableName1 + @CrLf + char(9) + @TableName2 + @CrLf + @CrLf print cast(GetDate() as varchar(20)) + @CrLf + @CrLf 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 ,IncludeInDataCompare varchar(5) ) 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 , ''' + case when @IncludeInDataCompare = 'True' and @ColumnList <> '' then 'False' else 'True' end + ''' 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 , ''' + case when @IncludeInDataCompare = 'True' and @ColumnList <> '' then 'False' else 'True' end + ''' 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 @debug = 1 begin select '#TableColumns' select * from #TableColumns end --determine which should be included in data compare based on passed column list while @ColumnList <> '' -- use all if no list provided begin -- already set all to "not in list" value, now update those in list to correct value Set @ColumnInList = case when charindex(',',@ColumnList)> 0 then substring(@ColumnList,1,charindex(',',@ColumnList) - 1) else @ColumnList end set @ColumnList = case when charindex(',',@ColumnList)> 0 then substring(@ColumnList,charindex(',',@ColumnList) + 1,DataLength(@ColumnList)) else '' end update #TableColumns set IncludeInDataCompare = @IncludeInDataCompare where COLUMN_NAME = @ColumnInList end if @debug = 1 begin select '#TableColumns afer IncludeExclude added' select * from #TableColumns end 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 'Tables have different structure' 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 END else print 'Tables have same structure' + @CrLf + @CrLf IF @CompareData = 'True' begin ----------------------------------------------------------------------------------------- -- Check for the presence of timestamp column ----------------------------------------------------------------------------------------- -- NOTE: This First Method is a simple method to check Whether Both Tables are Identitical. Print 'Data comparison 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 and IncludeInDataCompare = 'True' ) 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 = 'true' 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 IncludeInDataCompare = 'True' 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 and IncludeInDataCompare = 'True') SET @FieldList= SUBSTRING(@FieldList, 2, LEN(@FieldList)-1) SET @ConditionList= SUBSTRING(@ConditionList, 4, LEN(@ConditionList)-3) if @debug = 1 begin select @FieldList [@FieldList] select @ConditionList [@ConditionList] end -- 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' if @debug = 1 select @SQLStr [#Table_Index statement] exec (@SQLstr) if @debug = 1 select * from #Table_Index SET @Int1=0 SET @OrderBy ='' -- only of all columns in pkey are included in data caompare should we use the pkey here If not exists (select 1 from #TableColumns c join #Table_Index i on c.COLUMN_NAME = i.ColumnName where c.IncludeInDataCompare = 'False' and i.OrderId Is not null) begin 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) end IF @OrderBy ='' --No Primary Index Found SET @OrderBy =@FieldList ELSE SET @OrderBy= SUBSTRING(@OrderBy, 3, LEN(@OrderBy)-2) if @debug = 1 select @OrderBy [@OrderBy] SET @SQLStr='INSERT INTO #ROWCount_Table ' + 'Select Count(1),0,0, 0 ' + 'FROM ['+@SvrName1+'].['+@DBName1+'].['+@Schema1+'].['+@Table1+'] ' + @Filter + ' ' + 'update #ROWCount_Table set Int2 = [rowcount] from ' + '(select Count(1) [rowcount] FROM ['+@SvrName2+'].['+@DBName2+'].['+@Schema2+'].['+@Table2+'] ' + @Filter + ') T ' + 'Update #ROWCount_Table Set Int3= ' + '(Select Count(1) FROM (Select ' + @FieldList + ' ' + 'FROM ['+@SvrName1+'].['+@DBName1+'].['+@Schema1+'].['+@Table1+'] T ' + '' + @Filter + ' ' + 'UNION ' + 'Select '+ @FieldList +' ' + 'FROM ['+@SvrName2+'].['+@DBName2+'].['+@Schema2+'].['+@Table2+'] T ' + @Filter + ') A) ' + 'Update #ROWCount_Table Set Int4=(Select Count(1) FROM (Select '+ @OrderBy +' ' + 'FROM ['+@SvrName1+'].['+@DBName1+'].['+@Schema1+'].['+@Table1+'] T ' + '' + @Filter + ' ' + 'UNION ' + 'Select '+ @OrderBy +' ' + 'FROM ['+@SvrName2+'].['+@DBName2+'].['+@Schema2+'].['+@Table2+'] T ' + @Filter + ') A) ' if @debug = 1 select @SQLStr [#ROWCount_Table] exec (@SQLstr) if @debug = 1 select * from #ROWCount_Table Select @Int1=Int1, @Int2=Int2, @Int3=Int3, @Int4=Int4 FROM #ROWCount_Table IF @Int1=@Int3 and @Int2=@Int3 PRINT 'No Data Drift identified' else begin PRINT 'The Tables have different data' + @CrLf + '------------------------------------------------------' + @CrLf + 'Rowcount in '+@TableName1+ ' = '+Convert(Varchar(20), @Int1) + @CrLf + 'Rowcount in '+@TableName2+ ' = '+Convert(Varchar(20), @Int2) + @CrLf + 'Number of rows identical = '+Convert(Varchar(20), @Int1+@int2-@Int3) + @CrLf + 'Number of unmatched rows in '+@TableName1+ ' = '+Convert(Varchar(20),@int3-@Int2) + @CrLf + 'Number of unmatched rows in '+@TableName2+ ' = '+Convert(Varchar(20),@int3-@Int1) + @CrLf + @CrLf + 'Number of rows only in '+@TableName1+ ' = '+Convert(varchar(20), @Int4-@Int2) + @CrLf + 'Number of rows only in '+@TableName2+ ' = '+Convert(varchar(20), @Int4-@Int1) + @CrLf + 'Number of of matched rows by key with different data = '+Convert(varchar(20), @Int3-@Int4)+ @CrLf + '------------------------------------------------------' + @CrLf + 'Data Drift Check completed at '+Convert(varchar(20), GetDate(),114) + @CrLf + @CrLf ------------------------------------------------------------------------------------------ -- Now the Tables are not identitical. Now List all the Rows that are different -- ------------------------------------------------------------------------------------------ if @DisplayDataDrift = 'True' begin 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 ' + @Filter + ' UNION ALL Select '+ @FieldList+', 1 FROM ['+@SvrName2+'].['+@DBName2+'].['+@Schema2+'].['+@Table2+'] T ' + @Filter exec (@SQLstr) if @debug = 1 select * from ##Temp1 set @SQLSTR='Select '+ @FieldList+', SUM(Counter) as Instances into ##Temp2 FROM (Select * from ##Temp1) T Group By '+ @FieldList + ' Having SUM(Counter)<2 ' exec (@SQLSTR) if @debug = 1 select * from ##Temp2 set @SQLSTR='Select '''+@TableName2+''' TABLE_NAME, t.* into ##Temp3 FROM (select ' + @FieldList + ' from ['+@SvrName2+'].['+@DBName2+'].[' + @Schema2 + '].[' + @Table2 + '] T ' + @Filter + ') T ,(Select * from ##Temp2) a WHERE ' + @ConditionList exec (@SQLSTR) if @debug = 1 select * from ##Temp3 set @SQLSTR='Select '''+@TableName1+''' TABLE_NAME, t.* into ##Temp4 FROM (select ' + @FieldList + ' from ['+@SvrName1+'].['+@DBName1+'].[' + @Schema1 + '].[' + @Table1 + '] T ' + @Filter + ') T ,(Select * from ##Temp2) A WHERE '+ @ConditionList exec (@SQLSTR) if @debug = 1 select * from ##Temp4 set @SQLStr='Select t.* from ##Temp3 t UNION ALL Select t.* from ##Temp4 t Order by ' + @OrderBy exec (@SQLstr) end end Print 'Data Compare completed at '+Convert(varchar(20), GetDate(),114) end GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO