Schema Compare

Launching the Schema Compare Tool

The Schema Compare Tool provides users a method of depicting differences in the content of database schemas. Aqua Data Studio can compare any two registered databases regardless of vendor or version.  To compare two schemas, either multi-select individual schemas in the Schema Browser from connected databases, or launch the Schema Compare Tool from Tools Menu (Tools->Compare->Schema Compare) and browse to them. If the Schema Compare Tool is launched from the Tools Menu, it is possible to choose files that are not immediately visible in the Schema Browser, but are available. The Schema Object Types and Schema Objects to be compared can be selected before the comparison is run. Options to Include Object Permissions, Include Table Storage, and Order Table Columns by Name are also available before the comparison.

When the comparison is being performed, a status bar indicates its progress and runs in a background thread so that other operations in Aqua Data Studio can be performed.

Comparison Results

The top panel depicts differences in database objects. The top panel colors are: green-green (not different), pink-white (existing in the first database but not in the second database), white-green (existing in the second database, but not in the first database) half-green/half-pink (existing in both databases but different). Clicking a particular schema shows more specific details in the lower panel.

The schema listing for a resulting comparison has columns for type, schema, name, state, and difference count. These columns can be sorted one at a time by Ctrl + click on column headers. Their position left to right can be reorganized by dragging them into new positions.

If you click on the object, the difference will be depicted in detail on the bottom panel as a difference in their DDLs. The legend (pink, green, purple) explains those differences from the bottom panel. The objects highlighted in pink represent the objects that still exist in one database that have been deleted from the other database. The objects highlighted in green represent the objects that have been added to one database and not the other database. The objects highlighted in purple are the objects that exist in both databases but have been changed in some way in one or the other or both.

The results of a comparison display as a split-pane with line numbering down the center and connecting splines for identifying where the deletions, changes and additions occur. Each Schema in a comparison takes up half of the comparison results window and each can be vertically scrolled independently of the other. At the top, between the two schemas above the line numbering, digits separated by a / indicate the change selected out of total number of changes discovered (i.e. 9/27).  Comparisons show the character differences in a modified line with bolding and background color highlights indicate modified blocks. The large up and down arrows at the top right of the comparison results pane allow navigating through each difference.

Compare Schema Data

Once a Schema Comparison has been completed, it is possible to then compare the data contained within individual objects. Within the top pane, selecting an individual object, then clicking on the Compare Data icon (to the left of Filter Options) launches an additional comparison tab. This comparison tab functions identically to a query results comparison, allowing the data of two schemas to be compared. The primary key of the selected schema object (when applicable) is checked automatically for the results comparison generated by the Compare Schema Data button.

Object Compare

To compare two objects (schema DDL), multiselect two database objects from the Schema Browser, right click on one of them and select Compare.  The Object Compare feature allows for quickly discovering the differences between schema objects. It does not compare the schemas contained within those objects. Object Compare functions nearly identically to a file compare, with the objects compared being treated as individual text files.

Annotation Bar

The annotation bar provides a bird's eye view of all of the differences detected in a comparison. When differences are found, their corresponding highlight colors appear in the annotation bar on the right side of the results and can be used to quickly navigate to them. If a marker in the annotation bar is used to navigate to a particular difference by left clicking, a tick mark overlay shows which difference currently has focus.

The find button at the top left of the results allows quickly finding and highlighting content in the comparison.

Once the comparison is complete, it can be refreshed with the refresh button at the top left, allowing the same comparison to be run again.

Comparison Filters

When the "Enable Filters" checkbox is checked filters can be run on the existing comparison to help locate specific differences. The "Filter Results" button launches the Filters dialog, and the checkbox next to it runs those filters on the current comparison. Object Types of the compared schemas can be filtered within the Filter dialog for names and wildcards. If all that is needed is a viewing of the tables which are different, checking the other objects (tables will be deselected) and checking "Both Different" will show only those tables that are different in both schemas.  Schemas can be filtered for when:

• Both are different
• an item is unique to Server 1
• an item is unique to Server 2
• Both are equal

Saving Comparison Results as HTML

The results of a comparison can be saved as HTML for viewing later or for sharing with others. The "Save As HTML" button at the top left of the comparison window prompts for file destination and font selection. If the "Preview in Browser" icon is clicked the user is prompted to first "Save as HTML" for launching a web browser. If the comparison has already been saved as HTML, clicking "Preview in Browser" launches a browser with the stored HTML.