Use Navicat to compare and synchronize structure

Summary: Navicat compares objects between two databases and/or schemas and states the differences in structure, and synchronize the table structures and other objects.

Table of Contents

About Structure Synchronization

Navicat allows you to compare and modify the table structures and other objects with detailed analytical process. In other words, Navicat compares objects between two databases and/or schemas and states the differences in structure. To open the Structure Synchronization window, choose Tools -> Structure Synchronization from the menu bar.

You can save your settings as a profile for future use. To open a saved profile, click the Load Profile button and select a profile from the list.

HINT: Profiles are saved under the Profiles Location.

NOTE: Available only for MySQL, Oracle, PostgreSQL, SQL Server and MariaDB. Navicat Premium and Navicat for MySQL support synchronizing between MySQL and MariaDB.

Choose Connections & Comparing Options

Choose Source and Target Connections

In the Structure Synchronization window, define connections, databases and/or schemas for Source Database and Target Database. You can click to swap the source and target settings.

Choose Comparing Options

Then, click the Options button to set the database/schema comparing options. The options depend on the connection server type and sort in ascending order.

Compare auto increment value

Check this option if you want to compare the auto increment values of tables.

Compare character set

Check this option if you want to compare the character sets of tables.

Compare checks

Check this option if you want to compare checks.

Compare collation

Check this option if you want to compare the collations of tables.

Compare definers

Check this option if you want to compare the definers.

Compare events

Check this option if you want to compare events.

Compare excludes

Check this option if you want to compare table excludes.

Compare foreign keys

Check this option if you want to compare table foreign keys.

Compare functions

Check this option if you want to compare functions.

Compare identity last value

Check this option if you want to compare the identity last values of tables.

Compare indexes

Check this option if you want to compare indexes.

Compare owners

Check this option if you want to compare the owners of the objects.

Compare partitions

Check this option if you want to compare table partitions.

Compare primary keys

Check this option if you want to compare table primary keys.

Compare rules

Check this option if you want to compare rules.

Compare sequences

Check this option if you want to compare sequences.

Compare storage

Check this option if you want to compare table storages.

Compare table options

Check this option if you want to compare other table options.

Compare tables

Check this option if you want to compare tables.

Compare tablespace and physical attributes

Check this option if you want to compare tablespace and physical attributes.

Compare triggers

Check this option if you want to compare triggers.

Compare uniques

Check this option if you want to compare table uniques.

Compare views

Check this option if you want to compare views.

Drop with CASCADE

Check this option if you want to drop the dependent database objects with the CASCADE option.

Identifier Case Sensitivity

Ignore or consider the case of identifiers when mapping, or use the server default setting.

Start Comparison

Click the Compare button to compare the source and target databases.

View Structure Comparison Results

After comparing structures, the tree view shows the differences between the source and target databases or schemas. All objects are checked in the tree view by default. Uncheck the objects you do not want to apply to the target. You can expand the table objects to view the detailed structure.

You can choose to group the objects in the tree views by object types or operations by selecting Group by object type or Group by operation.

Operation	Description
	Object exists in both source and target databases/schemas, but they have different definition. The target object will be modified based on the source object.
	Object does not exist in the target database/schema. It will be created in the target.
	Object does not exist in the source database/schema. The target object will be deleted.
	Object exists in both source and target databases/schemas and they have identical definition. No operation will be applied.

When you selected an object in the tree view, the DDL Comparison tab shows the DDL statements of that object in the source and the target, and the Deployment Script tab shows the detailed SQL statements of the object that will be executed in the target databases.

Click the Next button to show the scripts of all selected objects.

Edit & Execute Selected Scripts

You can view all scripts that will be executed in the target database in the Deployment Script tab.

In the Edit Deployment Script window, use the arrow buttons to move the scripts.

Then, click the Start button to execute the scripts. The window will display the execution progress, execution time, and success or failure messages.