Schema comparison overview

1. On the Tools menu in Visual Studio, select SQL Server, and then select New Schema Comparison.

   Alternatively, right-click the TradeDev project in Solution Explorer, and select Schema Compare.

   The Schema Compare window opens, and Visual Studio automatically assigns it a name such as SqlSchemaCompare1.

   Two dropdown list menus with a green arrow in between them appear just below the Schema Compare window toolbar. These menus allow you to select database definitions for your comparison source and target.

2. In the Select Source dropdown list, choose Select Source and the Select Source Schema dialog opens.

   If you opened the Schema Compare window by right-clicking the project name, the source schema is already populated and you can proceed to step 4.

   

3. Complete the selections for a schema comparison source by choosing a Project, Database connection, or .dacpac file. The source is database definition that you want to use as the basis for changes to the target.

4. From the Select Target dropdown list in the Schema Compare Window , choose Select Target, and the Select Target Schema dialog opens. Complete the selections for a schema comparison target by choosing a Project, Database connection, or .dacpac file. The target is database definition that you want to evaluate and potentially apply changes to.

5. You can also select the Options button in the Schema Compare Window toolbar to specify which objects are compared, what types of differences are ignored, and other settings.

6. Select the Compare button in the Schema Compare Window toolbar to start the comparison process.

   When the comparison is complete, the structural differences between the project and the database appear in the Results pane in the upper part of the window. By default, the comparison results group all the differences are grouped by action (such as Delete, Change, or Add). The Results pane displays a row for each database object that differs between the database definitions. Each row identifies the object in the source or target schema (or both) and the action that would be taken on the target schema to make the target object the same as the source object. If an object was refactored and either renamed or moved to a new schema, the source and target names are different, and the source name appears in bold font to highlight the difference.

   

   By default the results list hides objects that are the same in both schemas or that aren't supported for update (for example, built-in objects). You can select the appropriate filter buttons in the tool bar to show these objects.

   To change the grouping preference, select the Group Results dropdown list in the toolbar. Select Type to group the results by object type (for example, by tables, views, or stored procedures).

7. By default all differences are included in the scope of the Update Target action. You can exclude differences that you don't want to synchronize. To do so, uncheck the Action column in the center of each row. Alternatively, right-click a row in the Schema pane, and select Exclude. The row is immediately grayed out. When schema compare is used to update the target database, this row isn't considered for any pending changes.

   You can also right-click on a group row and select Exclude All or Include All, which is equivalent to unchecking or checking all differences in that group. When you group results by schema this is a useful way to include or exclude all changes to a specific schema.

   If the row being excluded has any dependent objects (for example, a Table row that is referenced by a View row), the excluded row is disabled but its checkbox isn't cleared. Once all rows that depend on it are unchecked, the disabled row is unchecked. In addition, if a row is refactored (renamed or moved to another schema), then the checkbox is disabled for that row and any of its dependent child rows.

   If you refresh the comparison, those differences that you have chosen to skip are ignored.

To update the schema of the target, you have two options. You can update the target directly from the Schema Compare window if the target is a database or project, or you can generate an update script if the target is a database or a database file. A generated script appears in the Transact-SQL Editor, from which you can inspect the script execute it against a database.

Graphical schema comparison isn't yet available in the SDK-style SQL projects preview in Visual Studio. Use Azure Data Studio to compare schemas.

1. In Azure Data Studio in the command palette (ctrl/cmd+shift+P), search for and select Schema Compare.

   Alternatively, right-click a database project in the Database Projects view or a database in Object Explorer, and select Schema Compare.

   

   The Schema Compare window opens, and a source or target might be preset based on the launch point.

   Two ellipsis buttons with an arrow in between them appear just below the Schema Compare window toolbar. These menus allow you to select database definitions for your comparison source and target.

2. Selecting the ellipsis button for the source or target opens a dialog where either or both can be updated. Complete the selections for a schema comparison source by choosing a Project, Database connection, or .dacpac file. The source is database definition that you want to use as the basis for changes to the target. The target is database definition that you want to evaluate and potentially apply changes to.

   

   Once the selections are completed, select OK to close the dialog and return to the Schema Compare window.

3. You can also select the Options button in the Schema Compare Window toolbar to specify which objects are compared, what types of differences are ignored, and other settings.

4. Select the Compare button in the Schema Compare Window toolbar to start the comparison process.

   When the comparison is complete, the structural differences between the project and the database appear in the Results pane in the upper part of the window. By default, the comparison results group all the differences are grouped by action (such as Delete, Change, or Add). The Results pane displays a row for each database object that differs between the database definitions. Each row identifies the object in the source or target schema (or both) and the action that will be taken on the target schema to make the target object the same as the source object. If an object has been refactored and either renamed or moved to a new schema, the source and target names are different, and the source name appears in bold font to highlight the difference.

   

5. By default all differences are included in the scope of the Update Target action. You can exclude differences that you don't want to synchronize. To do so, uncheck the Action column in the center of each row. Alternatively, right-click a row in the Schema pane, and select Exclude. The row is immediately grayed out. When schema compare is used to update the target database, this row isn't considered for any pending changes.

   You can also right-click on a group row and select Exclude All or Include All, which is equivalent to unchecking or checking all differences in that group. When you group results by schema this is a useful way to include or exclude all changes to a specific schema.

   If the row being excluded has any dependent objects (for example, a Table row that is referenced by a View row), the excluded row is disabled but its checkbox isn't cleared. Once all rows that depend on it are unchecked, the disabled row will be unchecked. In addition, if a row is refactored (renamed or moved to another schema), then the checkbox is disabled for that row and any of its dependent child rows.

   If you refresh the comparison, those differences that you have chosen to skip will be ignored.

To update the schema of the target, you have two options. You can update the target directly from the Schema Compare window with the Apply button if the target is a database or project, or you can generate an update script if the target is a database with the Generate script button. A generated script appears in the Transact-SQL Editor, from which you can inspect the script execute it against a database.

Schema compare requires a graphical tool such as Visual Studio or Azure Data Studio.