DB Solo
Back to Index


Schema Comparison Tool

Schema Comparison Tool allows you to compare tables, views, functions and stored procedures between two schemas/databases. It will report any discrepancies between schemas such as missing or mismatching stored procedures, tables, table columns, indexes and constraints. It also will detect column discrepancies in the column data type, identity, nullability and default value. Comparison can be done between schemas on two different DBMS platforms such as Oracle and SQL Server.

You can invoke the Schema Comparison Tool from the 'Tools' menu in the main toolbar of DB Solo.

Object Selection

The first screen of the comparison tool allows you to select the schemas/databases to compare as well as individual objects within the selected schema. The tree control on the left hand side allows you to select 'source' of the comparison and the right side lets you pick the 'destination' schema.
After selecting the source schema/database you can select individual objects to compare within that schema. Comparable objecst include Tables, Views, Functions and Stored Procedures. By default all of these objects will be compared in the selected schemas/databases. The tabbed window at the bottom of the first screen allows you to hand-pick objects to compare. To do so, uncheck the 'Compare All' checkbox and check/uncheck the items you wish to compare.

Figure - Select Schema/Database Screen


After selecting the schemas/databases and the associated objects to compare, click on 'Next' to move to the settings panel.

Settings

The various tabs in the settings screen allow you to fine-tune the comparison process. All settings will get persisted when you close DB Solo, so you don't have to re-enter the same settings next time you want to run the comparison. There is a separate tab for each group of objects to compare.

Figure - Column Comparison Settings


After applying all the settings you wish to change, click on 'Next' to start the comparison. Notice that if the number of objects to compare is large , the comparison may potentially take several minutes to complete.

Results

After DB Solo has completed the comparison process, you will see the results presented as two tree contorls. Again, the left side is the 'source' schema/database and the right one is the 'destination' schema/database. The trees will contain all objects that were compared, unless the 'Only show objects present on both sides' checkbox is checked. In that case, the trees will only contain objects that were found in both schemas/databases.
When you select an object in either tree control, the 'Explain' panel at the bottom of the screen will give more details regarding the comparison results for the selected object. In the figure below, the data types of the selected column do match in the source and destination schemas, as indicated in the detail panel.

Each node in the tree controls is colored using the following coloring scheme.

Figure - Results Panel

Below each tree control are buttons that allow you to:

Viewing Results as Text

By clicking on the 'View Results as Text' button you can study the comparison results in a text window. This allows you to copy the results to system clipboard or save them into a file. If the appropriate checkbox was selected on the first screen, there will be a short summary section at the top of the results.

Figure - Viewing Results as Text

Schema Synchronization DDL Script Generator

The schema comparison tool has a Synchronization Script creator which allows you to create a DDL script that contains all required SQL statements to synchronize the schema or database you compared. If you generate the script for the source side, it will contain SQL statements that will make the source schema identical with the destination schema.

By clicking the Create Synchronize Script button on either side, you can invoke the Schema Synchronization Wizard Dialog.

The first screen of the dialog allows you to change the script generation settings. The settings screen will let you change the case of the SQL statements, the statement separator type and whether identifiers are shown in brackets, quotes or neither. Notice that these settings will not affect the source code of views, stored procedures or functions as they are not parsed by the tool.

You can also select if you wish to have comments or warnings in the output DDL script.

The last page of the Synchronization Script Wizard will show the DDL script based on the schema comparison results and your settings. The buttons on this screen let you save the script to a file or copy it to the system clipboard.

Loading & Saving Your Schema Compare Tool Project Files

The 'Load Project' button on the first page of the comparison tool allows you to open projects you have saved earlier. The project files contain information about the source / destination databases you have selected, objects you selected for comparison as well as all the comparison settings from the second page of the tool.

After completing a schema comparison, you can save your settings on the last page of the comparison tool. Clicking on the 'Save Project' button will bring up the 'Save Schema Comparison Project' dialog where you can select a file name for your project file as well as define the names of the comparison results' output files.

The first section of the page titled 'Project File' lets you select the name of the project file.

The second section of the screen contains fields for setting the file names for text-based results.

The third section contains fields for selecting the names of the synchronization DDL script files.

Notice that you must specify at least one output file for the command line comparison tool.

Schema Comparison Command-Line Tool

Schema comparison can be run from a command shell using settings from a saved project file. This allows you to run schema comparisons regularly using cron or a similar mechanism. The following shell command will run a schema comparison from settings in 'myproject.xml':

commandLine -compare myproject.xml

where 'myproject.xml' is a project file. You can use project files on different machines as long as the configured server connection names match. The project files are xml text files that can be edited manually outside DB Solo.

The command line tool will output errors and other messages in compare.log file.


Back to Index DB Solo
www.dbsolo.com
support@dbsolo.com