Use Tablediff to compare all tables

13,182

Solution 1

This can be done by setting the -sourceserver option of the tablediff utility to the first server and the -destinationserver option to the second server. You can use the sys.Tables to iterate through each table in the database so that you can automate this process.

Edited

I also wanted to point out this article which is a clever piece of t-sql code that may serve you better without the complications of tablediff

Per your comment, here is an example. This is not an optimal way to do this in a production enviorment, but it should get the job done for you. You would probably be better off implementing this in SSIS if you need a more production worthy option.

SET QUOTED_IDENTIFIER ON 

DECLARE @TableNames as table (
    id int identity(1,1),
    tableName varchar(100))

DECLARE @sTableDiff nvarchar(1000)
DECLARE @tableName varchar(100)
DECLARE @counter int
DECLARE @maxCount int

INSERT INTo @TableNames 
SELECT name 
FROM sysobjects WHERE type = 'U'

SET @counter = 1

SELECT @maxCount = COUNT(name) 
FROM sysobjects WHERE type = 'U'

WHILE @counter < @maxCount
    Begin
        SELECT @tableName = tableName 
        FROM @TableNames 
        WHERE id = @counter

        SET @sTableDiff= ' "C:\Program Files\Microsoft SQL Server\90\COM\tablediff" -sourceserver Server1 
            -sourceuser sa -sourcepassword password -sourcedatabase YourDatabase -sourcetable ' + @tableName + ' 
            -destinationserver Server2 -destinationuser sa -destinationpassword password -destinationdatabase 
            YourDatabase -destinationtable ' + @tableName + '  -f c:\Diff'      

        EXEC XP_CMDSHELL @sTableDiff

        Set @counter = @counter + 1
    End

Solution 2

Try dbForge Scheme Compare for SQL server and dbForge Data Compare for SQL Server. It can compare and synchronize any database data and scheme. Quick, easy, always delivering a correct result.

Run it up on you database!

Share:
13,182
Davie
Author by

Davie

Systems designer for an I.T. company in the UK.

Updated on June 13, 2022

Comments

  • Davie
    Davie over 1 year

    I have recently discovered the tablediff utility of SQL Server 2005.

    I have 2 instances of the same database each on a different server.

    Is it possible to compare all tables using tablediff without having to replicate the same command while only changing the table name?

    For example, compare table1 on server1 with table1 on server2 then compare table2 on server1 with table2 on server2, until all tables have been compared.

  • Davie
    Davie almost 14 years
    Can u provide an example? Thanks!
  • Davie
    Davie almost 14 years
    When I run this sql script there is an error where tablediff says it cant connect to the database. However, when I run the exact same query in a dos prompt it works without any problems i.e tablediff reports that the 2 tables are identical! Any ideas?
  • Irwin M. Fletcher
    Irwin M. Fletcher almost 14 years
    I am not sure, this is a script that I have used many times, so I know that it works. If you add Print @sTableDiff to the script and then execute the output in the command prompt, does that work?
  • Christian Severin
    Christian Severin about 13 years
    +1 and a "bump!" for the linked article at weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx and the elegant comparison query shown therein.