55

What is a free tool to compare two Sql Server tables (data and schema).

It would be great if the tool can script the differences found.

I also went through some older posts. The closest I have seen is SQLDBDiff but I would love to try more options.

Community
  • 1
  • 1
George
  • 561
  • 1
  • 5
  • 5
  • See already asked questions: - [Best tool for auto-generating SQL change scripts for SQL Server](http://stackoverflow.com/questions/10688/best-tool-for-auto-generating-sql-change-scripts-for-sql-server) - [Compare tools to generate update script for SQL server](http://stackoverflow.com/questions/176316/compare-tools-to-generate-update-script-for-sql-server) - [Anyone know of any good Database Diff tools?](http://stackoverflow.com/questions/104203/anyone-know-of-any-good-database-diff-tools) – Ray Nov 18 '08 at 19:41
  • Also check out our [SQL Schema Compare](http://www.xsql.com/products/sql_server_schema_compare/) and [SQL Data Compare](http://www.xsql.com/products/sql_server_data_compare/) tools which are free for SQL Server Express, no strings attached. – xSQL Software Dec 06 '13 at 15:42
  • I once made a tool to compare databases: https://github.com/alekkowalczyk/a7DbCompare – alek kowalczyk Oct 02 '15 at 13:40
  • Use Master Data extension for visual studio ........ https://visualstudiogallery.msdn.microsoft.com/530c546a-fdd3-4589-b2b2-e3c4aab13fba – Moumit Sep 16 '16 at 13:49
  • Just want to say @ray this has NOT been asked as the word here is FREE. – Dawesi Jul 16 '21 at 07:20

7 Answers7

58

TableDiff.exe should have everything you need. It is one of the hidden gems in SQL Server 2005. So you don't have to download anything.

• Table Difference tool allows you to discover and reconcile differences between a source and destination table or a view. Tablediff Utility can report differences on schema and data. The most popular feature of tablediff is the fact that it can generate a script that you can run on the destination that will reconcile differences between the tables. TableDiff.exe takes 2 sets of input; • Connectivity - Provide source and destination objects and connectivity information.

• Compare Options - Select one of the compare options • Compare schemas: Regular or Strict

• Compare using Rowcounts, Hashes or Column comparisons

• Generate difference scripts with I/U/D statements to synchronize destination to the source. TableDiff was intended for replication but can easily apply to any scenario where you need to compare data and schema. You can find more information about command line utilities and the Tablediff Utility in Books Online for SQL Server 2005.

Gulzar Nazim
  • 51,744
  • 26
  • 128
  • 170
  • 2
    Only installed if you choose replication features. (Considered a diagnostic tool, not an operational tool like sqlcmd. I hope that changes.) – yzorg Jan 22 '15 at 13:55
  • how it will be used for sql server 2008 and later versions ? i have installed sql server 2008. – Abdul Apr 23 '15 at 04:44
  • Refuses to compare schemas for me. The error being " have different schemas and cannot be compared" – Paul Mar 11 '16 at 09:13
  • 2
    SQL Table Diff is much better than TableDiff.exe. It has a gui and source code: https://code.google.com/archive/p/sqltablediff/downloads – Enkode Sep 28 '16 at 09:01
16

Even though this has been answered years ago a new comer which works really well is Data Compare within Visual Studio. It is part of Visual Studio 2010 and is part of the Database Professionals GDR update to Visual Studio 2008. This works great when you want to compare the same tables in 2 different databases.

If you need to compare 2 tables with the same schema that are in the same database or with different names TableDiff (mentioned earlier) is a great option.

oliwa
  • 1,834
  • 1
  • 16
  • 19
  • 5
    There's now SSDT: http://msdn.microsoft.com/en-gb/data/tools.aspx which has both schema compare and data compare as part of visual studio – RemarkLima Jul 09 '13 at 12:22
  • Powerful tool now integrated with VS2012. Allows you to take snapshots of a live database, work offline with that version and apply changes/test/debug procedures also. Schema comparison tool just what I required, all the .sql files exist locally - it generates them as part of a 'project', so much faster than the old 'Generate scripts' tool in SQL Server Management Studio. Check out this very informative overview from Microsoft : http://www.bing.com/videos/watch/video/gert-drapers-demonstrates-sql-server-data-tools/1r7u8u0qb – barneymc Nov 30 '13 at 15:08
9

DBComparer is free and works well for MS SQL Server

devmake
  • 5,222
  • 2
  • 28
  • 26
  • 2
    DBComparer does not compare data. – InActive Jul 18 '14 at 00:27
  • 3
    I just keep getting "Index out of bounds" when I try to select database after installing this on a new computer. Tried reinstalling, googling the problem etc... nothing helps. – Danieboy May 17 '18 at 06:38
2

Atlantis Interactiv, now owned by Pragmatic Works has two free tools called Schema Inspector and Data Inspector that works very much like the Red Gates tools.

Brad Rem
  • 6,036
  • 2
  • 25
  • 50
David
  • 316
  • 4
  • 7
0

We can recommend you a our reliable solutions for data and schema comparison for SQL Server: dbForge Schema Compare for SQL Server and dbForge Data Compare for SQL Server.

Their main advantages are high speed (up to 3 times quicker than most competitors) and extreme reliability.

Those tools are not free, but you can use 30-days trial for free and you have an opportunity to get a free license for both of the products - please refer to our free license conditions page.

Devart
  • 119,203
  • 23
  • 166
  • 186
0

Try CompareData from Zidsoft. It's free for comparing table/view data and has 30-trial for metadata comparisons

-1

A SQL Server specific database table diff tool is Volpet's Table Diff

Lorenzo
  • 4,558
  • 11
  • 44
  • 54