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