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.
To compare database definitions. On the Tools menu, select SQL Server, and then click New Schema Comparison. Alternatively, right-click the TradeDev project in Solution Explorer, and select Schema Compare. The Schema Compare window opens, and Visual Studio automatically assigns it a name such as SqlSchemaCompare1 .
What Database Compare can do. Use Database Compare to find design changes between two Access desktop database files or two versions of the same file. Database Compare alerts you to additions, deletions, or renaming of objects such as tables, queries, forms, reports, and VBA modules.
Buy red-gate's SQL Compare. You wil never regret spending the money.
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With