The Compare Schemas Wizard provides an easy to use interface for doing comparisons among two or more schemas. Based on your specifications, the wizard generates a script that you can save to a folder on your computer or run immediately to synchronize the two schemas. The wizard also enables you to add or check in the script to source control.
To run the wizard
- (If comparing from databases) Connect to the source and target databases that contain the schemas to be compared.
-
Do either of the following:
- From the Toad menu, select Database | Compare | Schemas.
- On the DB Misc tab of the Automation Designer, double-click (Compare Schemas) to add the action to your app, then double-click the action to open the Properties dialog.
-
The Choose Schemas page is shown. You can compare:
- One source schema or snapshot file to one or more target schemas or snapshot files
- Multiple pairs of schemas between a source and target database
- One or more pairs of snapshot files.
- Select one of the following sets of steps for the type of comparison that you want to run.
To compare one source schema/snapshot file to one or more target schemas/snapshot files
-
Under Source, select Database or Snapshot, then select the connection and schema or snapshot file as appropriate.
-
Do one of the following under Target:
To select one target at a time:
- Click .
-
In the Add Schema dialog, select either Database or Snapshot, then select the target connection and schema or snapshot file as appropriate.
- Click OK to add the selection to the Connection/File list.
- To add another target, click then repeat the steps.
To select multiple targets at once:
-
Under Target, click the arrow next to then select Add Multiple Schemas.
- Select Same Database to compare different schemas within the same database, or select Different Databases to compare the same schema from different databases.
-
Do one of the following, depending on what you selected in the previous step:
-
(If you selected Same database) Select the database under Connection, then double-click the schemas under Available Schemas to move them to the Selected Schemas list.
-
(If you selected Different databases) Select the comparison schema under Schema, then double-click the target connections from Available Connections to move them to the Selected Connections list.
-
- Click OK to add the selection to the Connection/File list.
- Click Next.
- Select options from the Choose Options window. See Choose Options
- Click Compare to run the comparison.
- See Using the Comparison Results.
To compare multiple pairs of schemas between a source and target database
- Under Source, select Database.
- Under Source, select the source database under Connection, then select <multiple schemas> from the Schema dropdown.
-
Under Target, select the target database under Connection.
-
(Optional) Under Target, use the Schema filter box to match the schemas to a particular pattern. For example, in the screenshot below, I could change the filter to "COMP%" to only compare the schemas that begin with the letters COMP.
- Select Match schema names automatically to have Toad automatically match the source and target schemas by name when you run the comparison. The match is shown in the lower half of the window. (Note: Built-in schemas like SYS, SYSTEM, ORDSYS, etc, are excluded. Look for "MultipleSchemasToIgnore" in the Toad.ini file if you want to edit the list of schemas that Toad will ignore).
- Deselect Match schema names automatically to choose the schemas to compare manually. Toad will display the schemas found in the Source and Target Connections, and it will check the ones it finds that exist in both schemas. You can change the Target schema if you want to match a source to a schema by a different name, and you can check/uncheck in the Include column to indicate which schema pairs to compare.
- Click Next.
- Select options from the Choose Options window. See Choose Options
- Click Compare to run the comparison.
- See Using the Comparison Results.
To compare one or more pairs of snapshot files
- Under Source, select Snapshot then select <Multiple Files> for Snapshot File.
-
Do one of the following:
- Specify Files: Click click to add a pair of snapshot files to the comparison. Repeat this step as many times as needed to add all of the source-target snapshot file pairs.
- Specify folders: Select this option to specify source and target folders that contain the snapshot files. Use this option only if all of your source files are in one folder and all of your target files are in another folder, and the pairs of files to be compared have the same file names.
- Click Next.
- Select options from the Choose Options window. See Choose Options
- Click Compare to run the comparison.
- See Using the Comparison Results.
Choose Options
Note: You can right-click to select Check All or Uncheck All and Expand All or Collapse All if needed.
Option |
Description |
---|---|
Compare | Object Types |
Select the object types that you want to compare. Unchecked items are ignored. Note: You can right-click to select Check All or Uncheck All and Expand All or Collapse All if needed. |
Compare | Output |
Output of the synchronization script is optional and can be saved later, but for multiple comparisons it is more convenient to configure output than to save the files later.
|
Options | Objects |
Select additional comparison criteria for objects that you are comparing. |
Options | Storage |
Select which storage attributes to compare. |
Options | Script |
Select the items to include in the synchronization script. |
Filters |
Further refines the object selection. Compare Object Names: Compares only objects that have a name that matches (or does not match) the pattern you supply. Maximum number of differences: Stops the comparison if the specified number of differences is found in a pair of schemas. Exclude File: Prevents certain objects from appearing in the synchronization script (details on how to create an exclude file are given in the "Compare and Review" section. Object Set: Lists which objects are to be compared. If no objects of a particular type are listed here, then all objects of that type will be compared. For example, suppose you selected both "Tables" and "Views" in "Object Types", and in the Object Set grid, you listed three tables. Of all the tables in the schema, only those three will be compared. Because you did not specify any views in the Object Set, all views in the schema will be compared. |
Using the Comparison Results
When the initial comparison is complete, the bottom half of the window shows the results for the selected pair of schemas on a series of tabs.
- Right-click on an object on the Difference Details tab to view additional options.
- To exclude an object from the synchronization script, uncheck it in the Difference Details list. You can then create an exclusions list by right-clicking in the window, then selecting Create Exclusion File from unchecked items. A file will be produced based on your selection. This file contains further instructions on how to edit it, if needed. To filter based on the exclusion file, click Back to go back to the Options page, then specify the file under Exclude File. You can then run the comparison again with the objects excluded.
- To find a particular object in the Difference Details, click anywhere within the list and then press CTRL+F on your keyboard to get a filter dialog. Press ESC to undo the filter.