What are the challenges you face when working across database platforms? Take the survey
Options

Comparing Two Schemas with different names

ChiprockChiprock Posts: 1 New member
We have a database that exists in multiple environments however due to the way the ERP sets the naming convention up they have schemas with different names.  These schemas have the same tables however.  I believe this is more common with Oracle databases, which makes some sense, as this product is available with a Oracle DB as well.  We are in SQL however...  So the databases look something like this.

Training Database
TRAININGDB.TRAIN.Table1
TRAININGDB.TRAIN.Table2
TRAININGDB.TRAIN.Table3

Development Database
DEVDB.DEV.Table1
DEVDB.DEV.Table2
DEVDB.DEV.Table3

They both contain the same data but the Schema is different.  So "TRAIN" is the name of the schema in Training and "DEV" is the naming of the schema in Development.  So the comparison would look something like this.

TRAININGDB.TRAIN.Table1 = DEVDB.DEV.Table1
TRAININGDB.TRAIN.Table2 = DEVDB.DEV.Table2
TRAININGDB.TRAIN.Table3 = DEVDB.DEV.Table3

The SQL compare tool does not allow me to say one schema is equal to another before doing the compare.  So TRAIN = DEV.  Furthermore there are around 6,000 tables in this DB I want to compare so things like synonyms and such are not a good option.

Does anyone know how to get SQL compare to compare these two schemas and show me differences?

10,000 Awesome points for anyone who can help out.

Thanks in advance for your help.
Tagged:

Answers

  • Options
    Eddie DEddie D Posts: 1,793 Rose Gold 5
    Hi, thank you for your forum post.

    Have you looked at the Owner Mapping feature of SQL Compare?  By default SQL Compare will automatically match objects names and schema that have the same name.  As you need to map objects of different schema names, the Owner Mapping will allow you to map together schemas of different names.

    When creating a new project or edit an existing project ->Select the Owner Mapping Tab and map the required schemas before comparing. 

    Further reading in this SQL Compare help article.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
Sign In or Register to comment.