How to exclude synonyms from schema comparison

Last modified: November 8, 2024

When working with a source database stored as a scripts folder in a Git repository, the database may not be specified explicitly in the script but can be defined using a variable within the synonym name. dbForge Schema Compare automatically detects any differences for synchronization, including those involving synonym variables. However, synchronizing these changes may cause the synonym to stop working. In such cases, it is recommended to ignore this difference by activating the Ignore database and server names for synonyms option.

This guide explains how to exclude synonyms from schema comparison before generating a synchronization script.

Workflow

• Comparing a scripts folder and a target database
• Excluding a synonym from schema comparison

Prerequisites

We’ll use the following scripts as samples:

Synonym in the source scripts folder

CREATE SYNONYM [dbo].[Synonym_production_products] FOR [$(BS)].[production].[products]

Synonym in the target database

CREATE SYNONYM [dbo].[Synonym_production_products] FOR [BicycleStore].[production].[products]

Comparing a scripts folder and a target database

To begin, we’ll run a schema comparison between a scripts folder, containing a variable for the database name in the synonym, and a database.

1. On the toolbar, select New Schema Comparison to open the New Schema Comparison wizard.

2. On the Source and Target page of the wizard, specify the source and target details:

• Under Source:
  • In the Type field, choose Scripts Folder as a source type.
  • In the Database scripts folder field, specify the path to the scripts folder.
• Under Target:
  • In the Type field, choose Database as a target type.
  • In the Connection field, choose the server connection on which the target database is located.
  • In the Database field, choose the target database.

3. To start schema comparison, select Compare.

The schema comparison document opens, displaying the objects grouped by status. dbForge Schema Compare has detected the differences in the synonyms, which appear in the Different section. The differences are highlighted in Text Compare.

Excluding a synonym from schema comparison

To exclude a synonym from schema comparison:

1. Return to the New Schema Comparison wizard by selecting Edit Comparison on the toolbar.

2. In the wizard, navigate to the Options page and select the Ignore database and server names for synonyms checkbox.

3. To start schema comparison, select Compare.

The schema comparison document opens, displaying the objects grouped by status. The synonym database objects now appear in the Identical section, indicating they won’t be synchronized. When selecting the synonyms, their database names are not highlighted in Text Compare because dbForge Schema Compare has ignored them based on the specified comparison options.