CODEWITHSUNDEEP
sqlsql-server
Davie
Davie

Reputation: 838

Use Tablediff to compare all tables

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.

Upvotes: 7

Views: 14247

Answers (3)

Devart
Devart

Reputation: 122002

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!

Upvotes: 1

Irwin M. Fletcher
Irwin M. Fletcher

Reputation: 2666

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

Upvotes: 10

Tamil.SQL
Tamil.SQL

Reputation: 262

Can you make use this option to run the sql statements in a script file and compare? I may be wrong just a thought.

-bf number_of_statements

Upvotes: 0

Related Questions