Sunday, June 28, 2009

Howto compare all database tables using T-SQL

Extension on post from Jeff on comparing tables.
By looping through the INFORMATION_SCHEMA.TABLES collection and construct the T-SQL
along the way, you have a simple and easy method for comparing databases.

USE = your_source_db-for_comparison_here --(to select the right tables from sys.objects)
GO

DECLARE @compareschema NVARCHAR(100)
DECLARE @comparetable1 NVARCHAR(400), @comparetable2 NVARCHAR(400)
DECLARE @dbtocompare NVARCHAR(200), @command NVARCHAR(4000)

SET @dbtocompare ='dbtocompare' -- db to compare
-- or set @dbtocompare = 'LINKEDSQLSERVER.dbtocompare' -- or db plus linked server if required

DECLARE tablenames_cursor CURSOR FAST_FORWARD LOCAL FOR
SELECT TABLE_SCHEMA,TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME

OPEN tablenames_cursor

FETCH NEXT FROM tablenames_cursor INTO @compareschema, @comparetable1
WHILE (@@FETCH_STATUS <> -1)
BEGIN
SET @comparetable1= @compareschema + '.' + @comparetable1
SET @comparetable2 = @dbtocompare + '.' + @comparetable1

PRINT @comparetable1 -- print tables in the messages window/tab
PRINT @comparetable2

SET @command = 'select ''' + @comparetable1 + ''' CompTable, * from
(select * from '
+ @comparetable1 + '
except
select * from '
+ @comparetable2 + ') as CompTable
union all
select '
'' + @comparetable2 + ''' CompTable, * from
(select * from '
+ @comparetable2 + '
except
select * from '
+ @comparetable1 +') as CompTable '

EXEC SP_EXECUTESQL @command

FETCH NEXT FROM tablenames_cursor INTO @compareschema, @comparetable1
END

CLOSE tablenames_cursor
DEALLOCATE tablenames_cursor

Because of the 'except' this construction works for SQL Server 2005 and higher only, and you can't use the the -older- text, ntext and image datatypes in comparisons.
You have to convert them to the newer varchar(max), nvarchar(max), and varbinary(max) data types first. (see my post on SQL Server 2000 post upgrade steps)

This is the error you get when you do use older data types
Msg 421, Level 16, State 1, Line 1
The text data type cannot be selected as DISTINCT because it is not comparable.
Msg 402, Level 16, State 1, Line 1
The data types text and text are incompatible in the is operator.



Bookmark and Share