Compare contents of two tables.

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

A WordPress.com Website.

Up ↑

%d bloggers like this: