Blog

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CompareTables]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_CompareTables]
GO

CREATE PROCEDURE sp_CompareTables
	@table1 sysname, 
	@table2 sysname, 
	@ColumnList varchar(8000) = '*',
	@Summary bit = 0,
	@Detail bit = 1,
	@TableNameHeader varchar(30) = '[Table Name]',
	@CounterHeader varchar(30) = '[Counter]',
	@rc int = null out
AS
------------------------------------------------------------------------------
--Compare the contents of two tables
--@table1 and @table2 are the table names
--@ColumnList is the list of column names, by default all columns will be compared
--@Summary - set this to 1 it you want summary information
--@Detail - set this to 1 (the default) if you want the details of the differences
--@TableNameHeader - this is used as the column heading the a column that contains the table name
--@CounterHeader - this is used as the column heading the a column that contains a row count 
--(this is used when there may be multiple identical rows
--@rc - the number of rows that are different
--Note: this SP can "live" in the master DB
------------------------------------------------------------------------------
DECLARE @err integer,
		@SQL1 varchar(8000),
		@SQL2 varchar(8000),
		@SQL3 varchar(8000),
		@SQL4 varchar(8000),
		@SQLTop varchar(8000),
		@SQLBot varchar(8000),
		@SQLTop2 varchar(8000),
		@SQLBot2 varchar(8000),
		@ColumnGrouping varchar(8000), 
		@indexlist varchar(8000)

SET NOCOUNT ON

-- a bit of validation first
IF object_id(@table1) IS NULL
	BEGIN
		RAISERROR ('Table ''%s'' does not exist', 11, 1, @table1) WITH NOWAIT
		RETURN
	END

IF object_id(@table2) IS NULL
	BEGIN
		RAISERROR ('Table ''%s'' does not exist', 11, 1, @table2) WITH NOWAIT
		RETURN
	END

IF @table1 NOT LIKE '%.%'
	BEGIN
		SET @table1 = DB_NAME() + '..' + @table1
	END

IF @table2 NOT LIKE '%.%'
	BEGIN
		SET @table2 = DB_NAME() + '..' + @table2
	END

-- let's get a list of columns that have different collations ...
CREATE TABLE #CollationColumns (name sysname)

DECLARE @SQL varchar(8000)

set @SQL = 'INSERT #CollationColumns SELECT a.name 
	FROM %db1..syscolumns a
	INNER JOIN %db2..syscolumns b
	ON a.name = b.name COLLATE database_default
	WHERE a.id = object_id(''%t1'')
	AND b.id = object_id(''%t2'')
	AND a.collationid  b.collationid'
SET @SQL = replace(@SQL, '%db1', PARSENAME(@table1, 3))
SET @SQL = replace(@SQL, '%db2', PARSENAME(@table2, 3))
SET @SQL = replace(@SQL, '%t1', @table1)
SET @SQL = replace(@SQL, '%t2', @table2)
 
EXEC (@SQL)

-- If the first table is in the current database
-- we can look to see if there is a unique index
IF PARSENAME(@table1, 3) = DB_NAME()
	BEGIN
		SET @indexlist = NULL

		SELECT @indexlist = COALESCE(@indexlist + ', ', '') + '[' + c.name + ']'
		FROM syscolumns c
		INNER JOIN sysindexkeys k ON c.colid = k.colid 
				AND k.id = OBJECT_ID(@table1) 
				AND c.id = k.id
		INNER JOIN sysindexes i ON i.id = k.id 
				AND i.indid = k.indid
		WHERE i.indid = ( SELECT TOP 1 i.indid -- get the first unique index (favouring the clustered one)
							FROM sysindexes 
							WHERE id = OBJECT_ID(@table1) 
								AND INDEXPROPERTY(i.id,i.name, 'IsUnique') = 1 
								AND INDEXPROPERTY(i.id,i.name, 'IsStatistics') = 0
							ORDER BY INDEXPROPERTY(i.id,i.name, 'IsClustered') DESC)
		ORDER BY k.keyno
	END

IF @indexlist IS NULL AND @Summary = 1
	BEGIN
		RAISERROR ('Summary is not availabe as table ''%s'' has no unique index', 10, 1, @table1) WITH NOWAIT
		SET @Summary = 0
	END
 
-- Let's generate a list of columns ...
IF @ColumnList = '*' or @ColumnList = ''
	BEGIN
		-- we can only do this in the current database (this could be enhanced though)
		IF PARSENAME(@table1, 3)  DB_NAME()
			BEGIN
				RAISERROR ('Table ''%s'' is not in the current database', 11, 1, @table1) WITH NOWAIT
				RETURN
			END
		SET @ColumnList = NULL
		SET @ColumnGrouping = NULL
		-- get a comma delimited list
		-- convert text and netxt to varchar(8000)
		-- convert image to binary
		SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') + CASE 
						WHEN systypes.name IN ( 'text' ,'ntext')
						THEN 'CONVERT(VARCHAR(8000),' + '[' + syscolumns.name + ']' + ') AS ' 
						WHEN systypes.name ='image'
						THEN 'CONVERT(BINARY,' + '[' + syscolumns.name + ']' + ') AS ' 
						ELSE ''
						END
		+ '[' + syscolumns.name + ']'
		-- if the column has different collation the just use a database default
		+ CASE WHEN syscolumns.name IN (SELECT name COLLATE database_default FROM #CollationColumns)
			THEN ' collate database_default AS [' + syscolumns.name + ']'
			ELSE ''
			END
		FROM syscolumns INNER JOIN systypes ON syscolumns.xusertype = systypes.xusertype
		WHERE ID = object_id(@table1)
		ORDER BY colid

		-- we need a similar list of columns for the GROUP BY clause
		SELECT @ColumnGrouping = COALESCE(@ColumnGrouping + ', ', '') + CASE 
				WHEN systypes.name in ( 'text' ,'ntext')
				THEN 'convert(varchar(8000),' + '[' + syscolumns.name + ']' + ')'
				WHEN systypes.name = 'image' 
				THEN 'convert(binary,' + '[' + syscolumns.name + ']' + ')'
				ELSE '[' + syscolumns.name + ']'
				END
		FROM syscolumns 
		INNER JOIN systypes on syscolumns.xusertype = systypes.xusertype
		WHERE id = object_id(@table1)
		ORDER BY colid
	END
ELSE
	BEGIN
		SET @ColumnGrouping = @ColumnList
	END

DECLARE @Counter1 varchar(8000)
DECLARE @Counter2 varchar(8000)

-- if we have a unique index then we don't need a row count.
IF @indexlist IS NULL
	BEGIN
		SET @Counter1 = 'COUNT (*) AS ' + @CounterHeader + ', '
		SET @Counter2 = @CounterHeader + ', '
	END
ELSE
	BEGIN
		SET @Counter1 = ''
		SET @Counter2 = '' 
	END

-- here's the wizzy SQL (I will explain it sometime)
SET @SQL1 = 'SELECT ' + @Counter1 + '''' + @table1 + ''' AS ' + @TableNameHeader + ', ' + @ColumnList + ' FROM ' + @Table1 + char(13)
SET @SQL2 = ' GROUP BY ' + @ColumnGrouping + char(13)
SET @SQL3 = ' UNION ALL SELECT ' + @Counter1 + '''' + @table2 + ''' AS ' + @TableNameHeader + ', ' + @ColumnList + ' FROM ' + @Table2 + char(13)
SET @SQL4 = ' GROUP BY ' + @ColumnGrouping + char(13)
SET @SQLTop = 'SELECT ' + @Counter2 + ' Max(' + @TableNameHeader + ') AS ' + @TableNameHeader + ', ' + @ColumnList + ' FROM (' + char(13)
SET @SQLBot = ') A ' + CHAR(13) + ' GROUP BY ' + @Counter2 + @ColumnGrouping + CHAR(13) + ' HAVING COUNT(*)  2'
SET @SQLTop2 = 'SELECT ' + @indexlist + 
				' , CASE COUNT(*) when 2 ' + 
				' THEN ''Different'' ' +
				' ELSE ''Only in '' + max(' + @TableNameHeader + ') ' +
				' END AS [Details] ' +
				' FROM ( '
SET @SQLBot2 = ') B GROUP BY ' + @indexlist

-- now output the details
IF @Detail = 1
	BEGIN
		EXEC (@SQLTop + @SQL1 + @SQL2 + @SQL3 + @SQL4 + @SQLBot)
		SET @rc = @@ROWCOUNT
	END

IF @Summary = 1
	BEGIN
		EXEC (@SQLTop2 + @SQLTop + @SQL1 + @SQL2 + @SQL3 + @SQL4 + @SQLBot + @SQLBot2)
		SET @rc = @@ROWCOUNT
	END

IF @rc = 0
	BEGIN
		RAISERROR ('Tables ''%s'' and ''%s'' are the same', 10, 1, @table1, @table2) WITH NOWAIT
	END
ELSE
	BEGIN
		RAISERROR ('Tables ''%s'' and ''%s'' contain %d ''different'' rows', 10, 1, @table1, @table2, @rc) WITH NOWAIT
	END


SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO