Applies to
ApexSQL Data Diff, ApexSQL Diff, ApexSQL Script
Summary
This article explains how to use the Schema mapping (aka owner mapping) feature to map objects with the same structure but different schema names.
Description
The Schema mapping feature can be used for custom mapping of database objects with different schema names without changing schema names in any of databases. In this article, ApexSQL Data Diff will be used as an example tool, but the mapping customization is the same for ApexSQL Diff and ApexSQL Script.
|
Quick tip: Source and destination schemas with the same name will be automatically mapped to each other by default |
To open the Schema mapping dialog, in the New project window, set the source and destination database, expand the Advanced option tab and select the Schema mapping tab:
ApexSQL Diff will load all schemas and automatically map the ones with the same name:
Custom schema mapping
There are several cases for performing the custom schema mapping:
-
To add new schema mapping to the list of the existing schema mappings, click on the empty row (first one) on the left (source) side, and select the source schema from the drop-down list. Then, select the destination schema from the right (destination) side drop-down list and click the Add button on the upper left side of the list:
Newly created schema mapping will appear on the bottom of the Schema mapping list:
-
To exclude schema mapping from the list (unmap already mapped schemas), select the appropriate schema pair and click the Delete button from the upper left side of the window:
Quick tip:
Press and hold the CTRL key to select multiple schemas and exclude them at the same time, or use Delete all option from the right side to remove all existing schema mappings
The excluded schemas will no longer be on the Schema mapping list, or the list will be empty if the Delete all button was used.
-
To change some existing schema mapping, select the schema from the left (source) or the right (destination) side and change it to desired schema by selecting it from the drop-down list:
After selecting desired schema from the source or destination (source in this case) they will be shown as mapped:
Quick tip:
To restore all custom schema mappings, click the Default button from the right side
How to save schema mapping and re-use it again
The Schema mapping feature has an option to save custom made schema mappings and re-use it later, so there is no need to set custom schema mapping every time when the same databases are compared and the same comparison result is needed. Custom schema mapping will be saved as part of the project, along with all other changes that were made in that project (such as a set of checked/unchecked options or filtered objects). It can be done by clicking on the Save button in the bottom left corner of the Project window:
|
Quick tip: In ApexSQL Data Diff, project is saved with .axdd extension, as that is its default extension |
More about using a project file can be found here.
The Schema mapping feature has an option to save some particular custom-made schema mapping and re-use it later. That can be accomplished using the following steps:
-
When the mapping is set, use the Export button on the upper right side to save/export the created schema mapping list for later use:
-
After clicking the Export button, select a location to save schema mappings and specify the name of the schema mapping (Schema mapping Books in this case), then click the Save button:
-
To load already saved mapping, use the Import button from the upper right corner of the Schema mapping tab:
-
Select the saved schema mapping from its location and click the Open button:
FAQs:
Q: Can I map one schema twice to two different schemas?
A: Yes. One schema can be mapped twice, for example, first time it is mapped automatically with the schema with the same name, and second time custom with the schema with the different name:
Q: Can I add more schema mappings at once?
A: No. Schema mappings can be added only one-by-one.
Q: Can compare with custom schema mapping through CLI?
A: Yes, schema mappings can be used via the CLI with the owners mapping switch: /schema_mapping:schema_mapping_file_path (e.g. /schema_mapping:“Schema_mapping.axmp”). This switch will override any schema mappings that exists in a project that is being used. More about CLI switches can be found here.
