Create Schema Comparisons

Use Schema Compare to do the following:

Compare two current schemas on the same or different databases .
Compare a current schema with a historical (offline) snapshot of the schema.
Compare a schema with several schemas.

Troubleshooting: When creating the schema comparisons, user should be granted the following roles and permissions:

User must have dbo_owner role and no db_denydatareader role.
User must be granted at least VIEW_DEFINITION permission. See Edit User / Role Permissions for more information.

Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

To compare current SQL Server schemas

Select Tools | Compare | Schema Compare from the menu.

Note: You can also click .

Complete the wizard. Review the following for additional information:

Specify Source and Target Databases page	Description
Complete Source and Target information	As source or target objects you can select either a database or a snapshot. Click Add to select objects for comparison. After specifying objects for comparison the first one is automatically set as source. Click Set as source next to any object to make it source.
Select Source and Target(s) window	Description
Select source and targets	Select one or several databases under connections node. You can specify alias and color for each database. By default connection category color is used (if specified). Note: Toad allows to compare source object to several targets. The first selected object is automatically set as source. To make another object comparison source, select it and click Set as source.
Select Objects page	Description
Add objects	Complete the following steps to limit the comparison to selected objects: Click Add in the Schema Compare wizard. Specify types of objects in the Object Types pane. Notes: Use Advanced Search to achieve more precise results. You can search not only by date or object type or name (or part of it), but you can also specify a name (or part of it) of the higher level object to which the search object should belong to. To perform complex name search split the object name by dots and select Search in Name (split by dots) option. For example, you may find all columns named col1 belonging to the tables, that have "s" in their names - test.col1, sales.col1, etc. Some objects cannot be excluded from the comparison. Click Search. The matching objects display in the Search Results pane. Click in the upper right corner to open the Selected Objects pane. The Selected Objects pane displays the list of objects selected for the schema comparison, while the Search Results pane displays your current search results. For example, assume you search for tables and select three tables to add to the schema comparison. The tables are listed in the Selected Objects pane. When you perform another search, the previous table search disappears but the three selected tables remain in the Selected Objects pane. Notes: If you do not enable the Selected Objects pane, you can only select objects in the current Search Results to add to the comparison. You cannot select objects from multiple searches. To delete the object from the Selected Objects pane, click . Select the objects in the Search Results pane.

See comparison results. See View Schema Comparison Results for more information.

Tips:

Only one compare can be open at a time. To avoid losing your work, save the current compare before creating a new one.
You can create an offline snapshot and compare it with schemas. See Create Schema Snapshots for more information.
You can save schema comparison settings as a profile.