Previous Topic

Next Topic

Book Contents

Book Index

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.

Compare Multiple 1

Specify the following:

Selecting the Tables to be compared

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.