How to compare two databases?

Question

I have two databases that are similar, but not the same. DB 1 is the old one and DB2 is the updated one with lots of new tables, columns, procs, constraint etc.

I need to write an update script in order to update DB1 database. These databases have lots of tables and stored procedures. Is there any way to get the differences in two database other than manually.

Answer 1

Buy red-gate's SQL Compare. You wil never regret spending the money.

Answer 2

There are a number of software tools (e.g. Red Gate) that enable you to compare databases easily.

Also, try this script; it's free and it should also work.

Cheers

Answer 3

To pile on to everyone suggesting Red Gate's tool, it should be noted that this tool can generate scripts to update the second database.

The awesome thing about red gate (besides the fact that they're an SO sponsor...) is that if you have the SQL Toolbelt with an active subscription, you will automatically get any new tools added. They recently released SQL Source Control, and I got it included as part of my SQL Toolbelt subscription.