( Pro Edition )
The Schema Compare tool is a powerful tool to compare existing databases and database objects with other databases in order to get an idea of the differences or attempt to create an update script.
For available compare options, see here - for the dialog instead of the wizard, see here, for information on the Schema Compare Results, see here. Afterwards, you can collect a script to update your database.
Note:
•Things like "collation" will be lost during a cross-database-engine compare as these differ widely across database engines.
•When comparing to the same type of database engine, all object properties will be used.
Schema Compare Wizard
The Schema Compare tool can be reached via Tools | Schema Compare, the Tools toolbar button or a context click on the Database Navigator - either the root Schema node or any object or object list like Tables or Indices. If you select Compare from a single object or object list, you will get a different window - a dialog - instead of the wizard.
The Database Navigator with the context popup for the Schema node
The Schema node context option and the menu option both start the Schema Compare Wizard. A step-by-step interface to get you through the compare process.
The Schema Compare Wizard - Welcome page
Depending on whether you started the wizard from an existing connection, or from the toolbar, you need to select a source datasource. If you started from an existing connection, this conection is used as the source datasource.
As a source datasource you can use any supported native connectivity datasource. After using the Next button, you can select a target datasource, for which you can use any supprted native connectivity datasource as well. There's no ADO or ODBC connectivity in the Schema Compare tool.
Using Next again brings you to the Available Objects page.
The Schema Compare Wizard - Available Objects page
On the Available Objects page, you can select the objects to compare. Objects that aren't supported in both datasources, aren't shown - as these cannot be compared. If you use the same database engine for both the source and the target, all supported objects should be visible.
Note:
•The Constraints node, if available, doesn't list Primary Key constraints - these are compared "in-line" when comparing tables.
•When using different datasource types for source and destination, but both sources support Views (or similar source-code objects), the compare and update process might fail for that object if database engine specific language (SQL or used stored functions) features are used.
•Instead of using the Indices, Triggers or Constraints nodes to select so-called "sub-objects", you can choose to auto-compare these objects op the Options page (see below).
Options Page
There are several options available when comparing - below is an explanation of each option. Some options might not be appropriate for each compare, but all options are always visible (eg: sequence options when the target datasources doesn't support sequences).
General |
|
Reverse object lookup |
For any object in a list (eg: table), also perform a reverse lookup from the target datasource to the source datasource. That means, cycle the target datasource table list and check to see if there's a table missing in the source.
|
Ignore case |
No case-sensitivity when checking object names or performing a lookup.
|
Ignore owner |
For those database engines that support an owner when creating an object, ignore any differences in ownership.
|
Ignore "default" attribute |
Ignores Column defaults.
|
Ignore default character set/collation
|
Ignore database character set/collation. |
Use enhanced datatype mapping
|
Uses a more complex datatype mapping scheme, especially useful when comparing different DBMS types.
|
Treat (N)(Var)Char and (Var)Char datatypes as equal
|
Treat NVarChar and VarChar as equal, treat NChar and Char as equal datatypes. |
Table |
|
Ignore default table character set/collation
|
Ignore default table character set/collation |
Ignore column domains |
Don't try a domain-name lookup, but compare the raw datatypes instead.
|
Ignore column positions
|
Ignore the order of the columns in this table. |
Include table indices |
Auto-compare any indices for this table.
|
Include table constraints |
Auto-compare any constraints for this table.
|
Include table triggers |
Auto-compare any triggers for this table.
|
Ignore Primary Key name
|
Ignore the name of the constraint (eg: MySQL doesn't allow custom names for this constraint).
|
Sequence/Generator |
|
Ignore sequence value |
Ignore the current sequence/generator value.
|
Storage |
|
Ignore placement |
Ignore "tablespace" or "filegroup" for an object.
|
Ignore other attributes |
Ignore other storage related attributes, like "blocksize". |
After using the Next button, the wizard will fetch the source objects, open a target connection and fetch schema data to compare to your selected objects.
Afterwards, it will create a Schema Compare Results Window where you can check for messages, errors and changes and collect a change-script.
Schema Compare Dialog
The Schema Compare Dialog, reachable via context clicks on the Database Navigator - but on single objects or object lists (like Tables), supports the same options and can create the objects in the target database as well. The major difference is that the Target Schema part only let's you select from registered or known databases and servers (mostly applies to InterBase and Firebird).
Schema Compare Dialog - quick schema compare
After using the Compare! button, the same compare process is used as with the wizard.
After that, the results will be shown in the Schema Compare Results window.
Schema Compare Results
After the Compare process, a Results window like the one below is shown. You can filter your results with the Filter Report button. Next to each object name, there are three symbols of interest:
The object was added (does not exist in the target datasource)
The object is different
The object was removed (does not exist in the source datasource)
These same symbols are used to compare scripts.
Schema Compare Results Window - table "Accommodation" is selected
In the lower part of the window, you can see the details for the selected object. The Messages tab will always hold a short summary of the changes, for example:
Schema Compare Results - results summary
Additional tabs hold more detailed information.
In the top right part of the window, you can select the action take when collecting the "update script". Depending on the current status of the object, you can select one of the following, with a simple right-click of the mouse:
The Action Menu
Do Nothing |
Ignore this object when collecting the script. |
Create in Destination |
Attempt to migrate the object. |
Drop in Destination |
Remove the object from the target datasource. |
Modify in Destination |
Alter the target object. |
Merge with Destination |
Only add columns, not drop column in the target datasource. |
Note:
•Obviously, the "merge" option only makes sense for tables and views.
•If the option "Do Nothing" is the one selected, then the overview will show a blank line
Collecting a Script
By using the green Collect Script button, you can choose to create an update script for all objects or the current filtered list (default). This will generate a script, but will not run it.
If an attribute cannot be updated (like the "COLLATE" clause in InterBase), the script will show something similar to:
/* Object MYDOMAIN
Attribute "collation" cannot be modified */
After collecting the script, you should be able to review it and use it to update your database.