Schema Comparison Tool

Schema Comparison Tool allows you to compare tables, views, functions, sequences, packages, stored procedures and other database objects between two schemas/databases. It will report any discrepancies between schemas such as missing or mismatching stored procedures, tables, triggers, columns, indexes and constraints. It also will detect column discrepancies such as data type, nullability and defaults. Also, comparison can be done between schemas on two different DBMS platforms such as Oracle and SQL Server.

For detailed information regarding this feature, see the User Documentation.

Currently schema comparison is supported for Oracle, SQL Server, DB2, SQLite, PostgreSQL, HSQLDB, Vertica, MySQL and Sybase ASE/ASA.

The comparison can also be run on all supported platforms: Windows, Linux and MacOS.

Selecting Schemas/Databases

The first screen of the Schema Comparison Tool allows you to select the source and destination schemas or databases you want to compare. The database/schema to be compared can either be on a live database server or a file containing previously saved snapshot of a database/schema.

Active Database

To compare a database or schema on a live database server, select the 'Database/Schema' tab under 'Source' or 'Destination'. The tree control on the left hand side allows you to select 'source' database/schema of the comparison and the right side lets you pick the 'destination' database/schema.

Saved Snapshot

To compare a database or schema that you have earlier saved to a snapshot file, select the 'Snapshot' tab under 'Source' or 'Destination'. To open a previously saved snapshot file, click on the file folder icon and point to the snapshot file on your hard drive.

If you only want to compare certain tables from the source schema/database, you can do that by unchecking 'Compare All Tables' check box and picking the individual tables for comparison. By default, all tables from the source and destination schema/database are compared.

Using the same mechanism, you also need to select the view, functions and stored procedures you want to compare, by default all objects will be compared. After selecting all the desired objects for the database comparison, click on 'Next' to proceed to the setting page.

Settings

The settings panel gives more control over what attributes will be compared between the selected schmas or databases.

The settings panel is divided into several main categories; Columns, Constraints, Indexes, Triggers, Stored Procedures, Functions and Views. For each category there are numerous settings you can modify to affect how the comparison is done.

For example, column comparison can be modified to ignore the default values or nullability of all columns. For each column data type there are additional settings you can modify, such as precision/scale comparison for fixed point decimal values.

Notice that all settings will be persisted between DB Solo sessions, so you don't have to enter the same information every time you run the comparison. After choosing your settings for the database comparison, click on 'Next' to start the comparison process. Notice that if your database contains hundreds of objects, the database comparison process can take considerable time to complete.

Viewing Results

The results panel shows the results of the database comparison in a tree format. The tree items are colored so that pinpointing differences between the compared schemas becomes trivial.

By clicking an item in either tree of the database comparison tool's result window will show a detailed explanation about the selected item. If an item is selected in both trees, the database comparison tool will show the details on the last clicked item. The 'Only show tables present on both sides' check box allows you to filter the output so that only tables present in both databases are shown in the trees.

When selected, the 'Only show objects that do match' check box will filter out objects whose definitions match, i.e. objects that are displayed in green in the tree controls.

Generating Schema Synchronization Script

You can automatically generate a DDL script that will synchronize the differences between the two schemas you compared. This script will contain the required SQL ALTER, DROP and CREATE statements to reconcile the differences based on the database comparison results. Notice that if you compare 2 databases between different DBMS products, such as DB2 and Sybase, the synchronization script option is not available.

Saving and Loading Schema Comparison Projects

The schema comparison tool lets you save all your comparison selections to a project file. The project files can then be checked into your source control system and shared between different entities in your organization. This facilitates running comparisons on a regular basis to minimize costly mistakes.

To save your comparison settings, click on the 'Save Project' button on the last screen of the schema compare wizard. This will bring up the dialog below where you can enter the output file names that will be generated by the command line tool (see next section).

Running the Schema Comparison from Command Line

After saving your schema comparison project to an XML file, you can subsequently run your comparisons from the OS command line or shell. To invoke the comparison tool from the command line, enter the following command:

commandLine -compare myproject.xml

where 'myproject.xml' the name of the project file you had saved earlier. Notice that it's possible to use a project file that was created on a different machine, however the DB Solo installation that you invoke needs to have the same server connections configured as the DB Solo installation that created the project file. This is because the project file does not contain all the connection information, just the name of the server connection.

All errors and informational messages during the command line schema comparison are recorded in compare.log file.