Compare Objects

The Compare Objects tool is used to compare the definition of objects. It is mainly used to compare the definition of tables, however it has been configured to compare some other types of objects.

You run the Compare Objects tool from the Database Explorer using the compare icon on the Admin Toolbar. You will see this icon only if the Compare Objects function has been configured for your database and object.

The Compare Objects tool is part of the Extended Edition of AQT, which is a separately purchasable component of AQT. If you have not purchased the Extended Edition you can still use Compare Objects, however you will not be able to use the Compare all objects in schema option. Alternatively you may wish to use the "old" compare tables function that was part of AQT v6.

Using Compare Objects

AQT can compare objects other than tables. The description below explains how Compare Objects works for tables.

Compare

This specifies the objects to be compared.

• you can compare either a single object, or all objects in a schema.
• when comparing all objects in a schema, you can specify a Filter to limit the number of objects compared.
• if you are running in evaluation mode, you will only be able to compare a single object to another single object.
• the compare-to object can be in the same or different database to the object being compared.

What to Compare

The What to Compare box shows you all the components that make up the definition of the table. These are:

• the table attributes (tablespace, pagesize, description etc)
• the table columns
• the "related objects" of the table (indexes, primary key, foreign keys etc).

Select or de-select components to specify what to include in the compare.

Each of these components is defined in AQT as a set of parameters. When you click on one of these components (in the left box) you will be shown these parameters in the Parameter for box:

• if the parameter is checked, AQT will compare that parameter
• if the parameter is not checked, AQT will not compare that parameter
• if the parameter has an asterisk * beside the name, the parameter is a compare parameter (this is described later in in How Object Compare works).

In general you should not change the Parameter compare settings from the default values.

Save / Open options

File > Save Options will save the details of the components and parameters you have selected to be compared. This can be reloaded with File > Open Options.

AQT can remember you options by automatically saving and opening your options. This is done by checking the Automatically Save / Open options option.

Other Options

• For columns, compare by column name. By default, when comparing columns, AQT will compare by column-number (the order it is defined in the table). If you aren't interested in the order of the columns in the table, you can check this option. The columns will then be compared by column name.
• Ignore case when comparing. When this is checked, AQT will ignore the case when comparing values. De-select this if you want to do case-dependent compares.
• Ignore schema names when comparing objects. This is a subtle option that is useful in some circumstances. If you are comparing tables in different schemas, the schema names for the tables is ignored. In other words SCHEMA1.TABLE1 will be matched and compared to SCHEMA2.TABLE1. However, the issue arises as to how to treat the "related" objects. If SCHEMA1.TABLE1 has a view SCHEMA1.VIEW01A and SCHEMA2.TABLE2 has a view SCHEMA2.VIEW01A, are these the "same" view that needs to be compared, or are they "different" views?

  By default, these will be considered as being different views, so will be flagged as being in one table and not the other. This is an appropriate way for the compare to be done if you are comparing schemas of the same names in different databases. If you are comparing different schemas, you may however want to match on view name only (eg. VIEW01A) and not schema/name. In this case, you should check this option. AQT will regard SCHEMA1.VIEW01A and SCHEMA2.VIEW01A as the same object and will compare these.

• Automatically Save / Open options. When this is specified:
  • the compare options are saved to disk whenever you either run a compare or close the window
  • when you open the window, AQT will check whether a saved-options file is present. If so, it will open these options.
  • the options file is compare_<database_type>_<object_type>.txt in your saved-queries directory. This means you will have a different options file per database type and object type.

Report File

AQT can generate a report file that details the differences between the objects. This can be used as a useful summary of the differences.

• Write report file. Specified whether a report file is to be generated
• report file name. Use this to specify the name of the report file. Click on View to view the report file in Notepad (it can be printed from there).
• Only report on differences. When this option is specified, information is written to the report file only for objects that are found to be different.
• Detailed report. This specifies that a detailed report for the objects is to be written.
• Show report when finished. When this option is selected, the report file will be displayed when the compare has completed.

When viewing the report file in Notepad it is recommended that a non-proportional font (such as Courier New or Fixedsys) is used. The information in the report file is aligned in particular columns, so will display better with these fonts.

You can have <schema> or <table> in your file name. When the compare is run, the schema and object-name of the object being compared are substituted for these. When comparing multiple objects, this allows you to have a different report file for each object.

Note that when you are comparing between different schemas, the Compare schema (eg. the first one specified) is the one substituted in <schema>.

Compare button

This runs the Compare, and shows you the results on the Results tab. To cancel the compare, click on the Abort button (you may neeed to go back to the Options tab to do this).

Viewing the Compare results

These are shown on the Results tab.

This window is divided into three parts, a top grid, and a left and right lower grids:

• thoughout the grids
  • a tick means the object compares successfully
  • a cross indicates that differences were found in the compare
  • a dot indicates that nothing was compared. You will see this for tables that are in one database/schema but not the other.
  • a zero indicates that there were no related objects for that table to compare.

Top grid

This shows you one row per table compared. For each row, there is a column in the grid showing you how each Component of the table compares. These correspond to the components selected in the What to Compare box in the Options tab.

• click on a tick or cross in the top grid to show you details of that compare in the left lower grid.
• clicking on a particular index in the left lower grids will show you details of the indexes in the right lower grid.
• click on View > Only Show Differences to only show rows in the top grid when the objects are different. This is handy of you are comparing a lot of objects, and just want to see the ones that are different.

Left lower grid

Shows details of the item highlighted in the top grid.

In the example of indexes:

• in the the left lower grid you will see the list of the indexes in the two tables.
• For each of these, the OK column shows you whether the index compares successfully or not. The Differences column gives a brief summary of what is different between the two indexes.

Right lower grid

• there is one row in the right lower grid per parameter being compared. This correspond to the Parameters for Index selection in the Options tab.
• the OK column show whether the parameters compare successfully.