Comparing all Tables in Schema

The Compare all Tables in Schema feature allows you to compare all the tables in a schema to tables in another schema or database in a single operation.

You can also generate a script to resync the contents of the tables.

Specify the following:

• Compare tables in schema. You specify here the schema/database containing the tables you are comparing.
• To tables in schema. You specify here where the schema/database the tables will be compared to.
• Match Tables. This field is used to match the names of the tables being compared. This is described later.

Selecting the Tables to be compared

• Ctrl+A will select all tables to be compared
• Ctrl+N will select no tables
• Filter can be used to select a set of tables
• Ctrl+L will select the tables used in the last compare. This is useful if you are rerunning the compare and the tables have been de-selected.

Match tables

The Match tables field is used to "match" the tables being compared.

If the names of your compare and compare-to tables are the same, then use a Match tables of <name> (this is the default). Then, for example, AQT will compare table SALES it the compare schema to table SALES in the compare-to schema.

However, in many cases these might be different. For instance, the tables in the compare-to schema might all be prefixed with "TEST_". In this case you want table SALES in the compare schema to be compared to table TEST_SALES in the compare-to schema. In this case specify a Match tables of TEST_<name>.

When you specify a value for Match tables, the Table2 column in the grid will be reloaded, and you will get a message as to whether the table exists in the schema.

The syntax for specifying Match tables is the same used in the Data Loader mapping.

Tables MUST have a Unique Key

The compare requires that all tables have a unique key. This can either be a primary key, unique index or user-defined key.

The compare will not be run for any table that does not have a unique key.

Compare Tables only

By default, compare will only compare tables (and not views, aliases etc).

If you wish to compare views as well as tables, deselect the Compare Tables only checkbox. The table list will now contain both tables and views.

As (for most databases) views cannot have a primary key or unique index, you will only be able to compare a view if a user-defined key has been defined for it.

Compare Options

The Compare all Tables in Schema function uses the Data Compare. On the Options tab you can specify the options used for the compare. These options are the same as those described in the Data Compare.

Running Multiple Compare in Batch

Compare all Tables in Schema can be run in batch (unattended mode).

To create a script statement to run the compare, use File > View Compare Script. Note that the script statement will include the tables you have selected to compare, so ensure that this is selected correctly before clicking on View Compare Script.

The script statement can be run from either the Run SQL window or included as part of a batch script.