Comparing Two Schemas with different names
Chiprock
Posts: 1 New member
in SQL Compare
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.
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
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
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com