Tip

Compare two SQL Server tables with stored procedures

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.
I use this to check what data changes when a set-based SQL statement is executed -- to find the new and modified entries.

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            
--

    Requires Free Membership to View

-- 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.

This was first published in February 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.