Schema Compare
Previous  Top  Next

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).

Option
Description
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 column domains
Don't try a domain-name lookup, but compare the raw datatypes instead.

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 sequence value
Ignore the current sequence/generator value.


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.