tablediff: howto compare all database tables

tablediff.exe is a command-line utility that you can you to compare tables from different database / servers
It is located in the ...\ Program Files\Microsoft SQL Server\90\COM folder.

It is a handy tool that can compare tables at an amazingly high speed and create sync scripts along the way.
Downside is that it doesn't accept wildcards like: compare all tables of a database,
you have to pass all object info to the tool via the command line.

The purpose of this T-SQL script is to build a full tablediff.exe command line for every object in the specified databases and print it in the messages window. You can then copy paste it in a .bat file and execute it from a cmd.exe window.

Log file and sync scripts are placed in c:\ folder with 'tablediff' + table name as file name and .sql or .txt as extention.

Tips;
Primary keys must be defined on all the tables for the tool to work ok
Sync'ing of blob datatypes (like varchar(max) or varbinary(max)) is not supported -unfortunately-,
You can use the bcp utility or a linked server to accomplish this.
Script presented here uses integrated security, so you have to log on with the right privileges
or use the SQL Server service account for the script to work ok.

USE your_db_here -- for selection of the objects from INFORMATION_SCHEMA.TABLES
go

DECLARE @compareschema NVARCHAR(100), @comparetable NVARCHAR(400)
DECLARE @sourceserver NVARCHAR(100), @destinationserver NVARCHAR(100)
DECLARE @sourcedatabase NVARCHAR(100), @destinationdatabase NVARCHAR(100)
DECLARE @tablediffpath NVARCHAR(512)
DECLARE @command NVARCHAR(4000)

SET @sourceserver = 'Server1'
SET @sourcedatabase = 'CodeCatalog' -- or db_name()
SET @destinationserver = 'Server2'
SET @destinationdatabase = 'CodeCatalog' -- or db_name(), if same name

-- set the right path here....
SET @tablediffpath= '"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe"'

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, @comparetable
WHILE (@@FETCH_STATUS <> -1)
BEGIN

SET @command = @tablediffpath + ' -sourceserver ' + @sourceserver + ' -sourcedatabase ' + @sourcedatabase + ' -sourceschema ' + @compareschema + ' -sourcetable ' +
@comparetable + ' -destinationserver ' + @destinationserver + ' -destinationdatabase ' + @destinationdatabase + ' -destinationschema ' + @compareschema +
' -destinationtable ' + @comparetable + ' -f C:\tablediff-' + @comparetable + '.sql' + ' -o C:\tablediff-' + @comparetable + '.txt'

PRINT @command -- output to messages window/tab

FETCH NEXT FROM tablenames_cursor INTO @compareschema, @comparetable
END

CLOSE tablenames_cursor
DEALLOCATE tablenames_cursor

Howto compare all database tables using T-SQL

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.