Easiest Way To Diff Two Table Schemas In SQL Server 2008?

Question

I have to do checks between a development and release database and do this manually, which is both slow and not 100% reliable (I only visually inspect the tables).

Is there a quick and easy way to compare table schemas automatically? Maybe even a feature that does this built right into SQL server?

Edit: I'm comparing structure only, thank you for pointing this out.

Answer 1

Data or structure or both? Try RedGate sql compare or data compare. They both have free trials and are awesome.

http://www.red-gate.com/products/SQL_Compare/index.htm

http://www.red-gate.com/products/SQL_Data_Compare/index.htm

Answer 2

I'm a fan of SQL DBDiff, which is an open source tool you can use to compare tables, views, functions, users, etc. of two instances of SQL Server databases and generate a change script between the source and destination databases.

Answer 3

For a free solution, you can use SQL Server Managements Objects to output the DDL script for each table, view, index, SP, UDF, etc. Then you can compare, either in code, or using a diff tool like WinMerge.

Answer 4

There are some commercial products that do this; Visual Studio 2010 Premium Edition can compare schemas.

Some others:
http://www.red-gate.com/products/SQL_Compare/index.htm
http://www.apexsql.com/sql_tools_diff.aspx

Answer 5

Soo,

Googled this:

for structures: http://thedailyreviewer.com/dbsoftware/view/diff-for-tables-structures-rather-than-data-105128346

see also: http://thedailyreviewer.com/dbsoftware/view/how-to-diff-the-data-of-same-table-of-two-sql-servers-107379347

Answer 6

You can use the SQL Management studio tools to "Generate scripts" from both databases. Then use your favorite text comparison tool to see any differences.

In the old days, this worked great, but in SQL 2005 the generate script code changed and the objects were no longer created in the same order, so the text comparison is less useful. I have not tested this in more recent versions of SQL so it may have been fixed. You can also try http://exportsqlscript.codeplex.com/ which I have used with good success to output the DDL as scripts for source code control and comparing versions.

References:

• https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=332400&wa=wsignin1.0
• http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/505fd238-e0dc-42ae-8a54-2dceace81bb3/
• http://exportsqlscript.codeplex.com/