Comparing differences in database structure between databases

I am trying to find a way to easily compare the difference in table/column structure between two SQL server databases.

I am doing this since I need to document the foreign key relationships between a database schema that is currently under development. The foreign keys are not defined as constraints in the database, but are controlled through the application.

The current naming conventions make it easy to see what the relationships are (primary keys are "tablename_seq" and foreign keys use the same names, only tables that have foreign keys that reference themselves break this rule, with a suffix added to the primary key name like "tablename_seq_parent").

In order to document what these relationships are, I have created a copy of the database and set up the foreign key restraints so that I can use the database digram tools in SQL Server Management Studio, or Visio. It took quite a bit of manual work to create all these relationships.

Now the developers have added new tables, or made changes to tables and I need to keep the document up to date. Manually keeping track of all the changes will probably be an issue so I am looking for either:

a way of automatically generating an update script for my database when comparing to the development database, so that I can update to the latest version (then manually create the new constraints OR a way of automatically reading in information on the tables from a database and creating foreign key relationships for any primary key that is a column of another table (ie. has the same name)

The closest thing I have found that might help solve the first option is the tablediff utility. I thought perhaps I could write a script (it has been a while!!) that:

Lists all tables in the developer database For each table check if it exists in my database If it does exist then use tablediff to check for new or changed columns and generate a script to change the table using -f. If it doesn't exist then create table using script If tables exist in the destination database but not in the main one then flag them for followup manually.

Does anyone know of a simpler way that I have missed?

Regards

Jo

Hi all

Does anyone have any ideas on this? Or have I posted to the wrong forum?

Regards

Jo

|||

Hi,

Easiest thing to do is to get a third-party tool for this. I personally use the SQL Tools from Red-Gate.

For what you want , its SQL Compare. Its 295 USD, and a total bargain. There are others, from ApexSQL, and DB Ghost...

However, I use all Red-Gate tools regularly and wouldn't be without them and for me, I plumped for the SQL Bundle Pro (990 USD).

Cheers.

Paul

|||

I use a product called AdeptSQL Diff, it's pretty quick to scan my databases (6500 procs and 1100 tables in about a minute) and easy to use. www.adeptsql.com

The comparison tool is 240 USD and if you want to compare your data as well, it's 320 USD.

Jarret

|||If nothing else, version your DB objects as scripts within VSS, and do a compare between script versions.