Database Compare

The 'Database Compare' tab provides a mechanism for comparing the current data model with a live database, and optionally synchronizing any differences in either direction. Differences 'pushed' into a live database are performed using 'Alter DDL' statements, while changes imported from the live database can be directly 'pulled' into the model.

The Database Compare functionality requires the Database Builder to have a valid ODBC connection to a live database. This database connection is shared by the 'SQL Scratch Pad', 'Database Compare' and 'Execute DDL' tabs of the Database Builder.

Access

Open the Database Builder window, then display the 'Database Compare' tab.

Ribbon

Specialize > Tools > Database Builder > Database Compare

The DDL Compare Tab

The 'Database Compare' tab has a number of controls, as described here.

Number & Name	Description
1 Case Sensitive	Click on this checkbox to make all comparisons of properties recognize differences in letter-case in the property text.
2 Use Alias if Available	Click on this checkbox to indicate that any defined aliases should be used instead of object names (at both object and column level).
3 Reset All	Click on this button to set the 'Action' flag for all objects back to the default value.
4 Set Import All	Click on this button to set the 'Action' flag of all detected differences to <====; that is, update the model with the value(s) from the live database.
5 Set Synchronize All	Click on this button to set the 'Action' flag of all detected differences to ====>; that is, update the live database with the value(s) from the model.
6 Differences	Review the list of objects found to have mis-matches between the model and the live database. Selecting an item in this list will populate the 'Components' list. (See the Differences List table for a detailed description of each column.)
7 Components	Review this list of properties of the selected object that differ between the model and the live database. (See the Component List table for a detailed description of each column.)
8 Reset	Click on this button to set the 'Action' flag for all properties of the current object back to the default value.
9 Import from ODBC	Click on this button to import all properties' values (with the 'Action' of <===) from the live database into the model.
10 Generate DDL	Click on this button to generate the 'Alter DDL' statements for all objects with an 'Action' of ====>, and send the statements to the 'Execute DDL' tab.

Differences List

Column	Description
EA	Displays the name of each object in the model that has one or more detected differences. Blank values indicate that the object is missing in the model but exists in the live database.
Action	Defaults to 'No Action' as the action to take considering this object's difference(s). Click on the drop-down arrow and select a specific action. The list of available actions in the list will depend on whether or not the given object is paired in the model and live database. Paired objects No Action - do not update the database or model with this change ===> - update the object in the database from the model <=== - update the object in the model from the database Customize - set the items to No Action prior to setting different actions on each item in the lower panel Unpair - separate the paired objects so that they are not compared with each other or updated from each other Unpaired objects Create <object name> - create the missing database object in the database or model, as appropriate Delete <object name> - delete the object from the model Drop <object name> - delete the object from the database Pair with <object name> - pair the object in the database with the named (unpaired) object in the model, so that they are compared for differences between them The 'Action' fields in the 'Components List' (the lower panel) will be updated based on the selection of this field. For example, if the live database has a Table column 'Address1' and the model doesn't, setting the object 'Action' to '===>' (update the object in the database from the model) sets the column 'Item Action' to 'Drop Address1', which will remove the column from the live database.
ODBC	Shows the name of each object in the live database that has one or more detected differences. Blank values indicate that the object exists in the model but is missing in the live database.
Count	Shows the total number of detected differences for the object (and all of its components) between the model and live database.

Component List

Column	Description
Item	Shows the component name or description for each detected difference. The differences are grouped into three categories: Properties, Columns and Constraints, in a tree structure.
EA	Shows the value of the given component as detected in the model. Blank values indicate that the value is missing in the model but exists in the live database.
Action	Defaults to the action corresponding to the setting of the object 'Action' field in the 'Differences' list, to indicate the action to take regarding the difference detected for the component. Click on the drop-down arrow to select an alternative action; the available options in the list depend on the component's type and the detected difference. No Action - do not update the database or model ===> - update the object in the live database from the model <=== - update the object in the model from the live database Add <item name> - create the missing item in the database or model, as appropriate Delete <item name> - delete the item from the model Drop <item name> - delete the item from the live database
ODBC	Shows the value for the selected component in the live database. Blank values indicate that the value exists in the model but is missing in the live database.
Count	Shows the number of differences between the model and the live database detected in the selected component.

Working with the Database Comparison

Whenever you perform a comparison, Enterprise Architect reads the definition from both the live database and the model, and then attempts to 'pair' each object from one source with the other, using its name (and schema, if relevant for the current DBMS).

If a match is found, the object name is shown in both the 'EA' and 'ODBC' columns with a default action of 'No Action'. The 'Count' column indicates the total number of differences found for the object and its components or properties.

If a match is not found between the systems, the object name is shown in the source column (either 'EA' or 'ODBC') while the other column is blank. In this state it is possible to pair the object with an object of a different name; the 'Action' dropdown list will present the available objects. If a new pairing is made the two objects' definitions are compared for differences and the results are shown in the 'Components' list, with the default action of '====>' selected.

If you select an action at the object level, this will set the matching action for all of the object's components and properties. However, if you select the 'Customize' action at the object level, you can determine a different action for each component.

As an example, both a column (tax_amount) and constraint (ck_customersordersitems_discount) were renamed in Table 'public.customers_order_items' (in the Example model) and a database compare performed; this image shows the differences found:

In the image there is only one Table that had detected differences - 'public.customers_order_items'; selecting this populates the 'Components' list. From the detected results it can be determined that the data model contains a column (tax_amount2) and a check constraint (ck_customerordersitems_discount1) that the live database doesn't and in turn the live database contains a column (tax_amount) and a check constraint (ck_customerordersitems_discount) that the data model doesn't.

Comparing with Options

The 'Compare with Options' functionality works in the same manner as for a direct comparison, except that you are prompted to choose which object/property comparisons should be performed. This enables you to ignore particular differences that are not of relevance at the current time.

These tables describe the different comparisons that can be enabled or disabled.

All Objects, Owner

Comparison	Action
Owner	Select to indicate that the 'Owner' property of all database objects should be compared, after the objects have been 'paired'.

Table Options

Option	Action
Tables	Select this parent option to enable all of the Table comparison options. Deselect to disable all the other options. You would then deselect or select specific options in the list.
Table - Extended Properties	Select to indicate that extended properties of Tables (such as DB Version and Tablespace) should be compared.
Table - Remarks	Select to indicate that remarks applied to Tables should be compared.
Columns	Select this parent option to enable all of the 'Column comparison' options. Deselect to disable all the other 'Column' options. You would then deselect or select specific options in the list.
Column - Type	Select to indicate that the datatype name for the Table Columns should be compared.
Column - Size	Select to indicate that the datatype size for the Table Columns should be compared.
Column - Default Value	Select to indicate that the default values of the Table Columns should be compared.
Column - Position	Select to indicate that the Table Column positions should be compared.
Column - Not Null	Select to indicate that the not null property of the Table Columns should be compared.
Column - Auto Numbering	Select to indicate that the autonumbering properties for the Table Columns should be compared (such as AutoNum, StartNum and Increment).
Column - Unmatched Columns	Select to indicate that Table Columns that are unmatched between the model and the live database should be compared. Typically these are columns that exist in one system but do not exist in the other.
Column - Extended Properties	Select to indicate that extended properties of Table Columns (such as Unsigned and Zerofill) should be compared.
Column - Remarks	Select to indicate that remarks applied to Table Columns should be compared.
Constraints	Select this parent option to enable all of the 'Table Constraint comparison' options. Deselect to disable all the 'Table Constraint' options. You would then deselect or select specific options in the list.
Constraint - Primary Keys	Select to indicate that properties related to Primary Keys should be compared.
Constraint - Foreign Keys	Select to indicate that properties related to Foreign Keys should be compared.
Constraint - Indexes	Select to indicate that properties related to Indexes should be compared.
Constraint - Unique Constraints	Select to indicate that properties related to Unique Constraints should be compared.
Constraint - Check Constraints	Select to indicate that properties related to Check Constraints should be compared.
Constraint - Table Triggers	Select to indicate that properties related to Table Triggers should be compared.
Constraint - Unmatched Constraints	Select to indicate that Table Constraints that are unmatched between the model and the live database should be compared. Typically these are constraints that exist in one system but do not exist in the other.
Constraints - Extended Properties	Select to indicate that extended properties of Table Constraints (such as Fill Factor and Clustered) should be compared.
Constraints - Remarks	Select to indicate that remarks applied to Table Constraints should be compared.

Notes

The Database Compare functionality currently can perform comparisons on Table, View, Procedure, Function and Sequence object types

Learn more