CODEWITHSUNDEEP
database-designsql-server-2012sqlcompareopendiff
King Code
King Code

Reputation: 61

How do I go about creating a script to compare , update and match structure of two databases

I am comparing two databases using open diff which is similar to SQL Compare.

I have two databases which should match in structure so as I compare I would like to construct a script such that if the one DB has a specific table that the other DB doesn't have it creates that table so that the structure and collation in the two databases matches.

I am using SQL Server 2012.

Upvotes: 0

Views: 265

Answers (1)

Walter Mitty
Walter Mitty

Reputation: 18940

There are a lot of ways to get this done. One way is to construct what I'll call a "metadatabase". A metadatabase is just a database whose user tables contain the structural metadata from one or more other databases. I'm not too familiar with SQL Server, but I did the same thing several times with Oracle.

Perhaps the easiest way is to unload a selection from the system tables, and load them into an MS Access DB, as user tables. From there, comparing two databases is just ordinary DML, which you should know how to do, if you're a DBA. Example: for all the columns in all the tables in both databases, group by table name and column name, and select the groups that have more than one datatype or more than one precision. Or select the groups that only exist in one database.

You could use a third SQL server database as the metadatabase container if you like. I just like MS Access because it's so easy.

A more detailed explanation would require more details about what you are trying to accomplish.

Upvotes: 1

Related Questions