Template to Create SQL Server Synonyms with checks

By: Svetlana Golovko   |   Updated: 2017-05-02   |   Comments   |   Related: > Synonyms

Problem

When a SQL Server synonym is created with T-SQL the base object's existence is checked only at run time. SQL Server Management Studio (SSMS) has some built-in checks that are performed during synonym creation whereas T-SQL scripts can be written several different ways and do not have any checks by default. What is the best way to create synonyms and why?

Solution

Synonyms became available in SQL Server since version 2005. One of the great uses of synonyms is described in this tip.

Synonyms similar to other SQL Server database objects could be created using the SSMS GUI or using T-SQL scripts. In this tip we will show the difference between the two methods and provide you a template for synonym creation.

Create a synonym with SSMS

To create a synonym with SSMS, under the database right click the Synonyms container and click "New Synonym...":

• Type a Synonym name
• Select "Database name" or keep default/current database if the base object is in the same database where synonym is created
• Select "Schema"
• Select "Object type" from the drop-down list
• Select "Object name" from the drop-down list (it will be populated after the Object Type is selected):

Note: the "Object name" drop-down list will be populated with object names only after the schema and object type are selected.

When you create a synonym via SSMS the dependent object in sys.synonyms catalog view will always have a three-part name (database_name.schema_name.object_name):

SELECT name, base_object_name FROM sys.synonyms
GO

When you use the selection options in SSMS for the database name, schema and object name you don't have to worry about the object's existence. But you can overwrite the database, schema and object name and type them in manually. In this case you run the risk of mistyping or entering a non-existent object. SSMS will create the synonym just fine, but it will reference a non-existing object.

Create a synonym with T-SQL

When a synonym is created using T-SQL for the base object residing in the same database it can be created with a two-part name instead of a three-part name:

CREATE SYNONYM [dbo].[TempSyn_2] FOR [dbo].[v3]
GO

In theory, neither the database name or schema name is required when T-SQL is used for the synonym creation. If the database name is not specified then the name of the current database is used. If the schema name is not specified then the default schema of the current user is used.

CREATE SYNONYM [dbo].[TempSyn_3] FOR [_Demo]..[Table_1]
GO
CREATE SYNONYM [dbo].[TempSyn_4] FOR [Table_1]
GO

Here is what we have as a result of querying sys.synonyms view:

SELECT name, base_object_name FROM sys.synonyms
GO

SSMS on the other hand will raise an error if you try to create a synonym without a schema name:

or without a database name:

SQL Server Synonym Best Practices

To make the base object name in the sys.synonyms view consistent you should always use three-part names when you create synonyms with T-SQL.  Here is what this data looks like if we query sys.synonyms, you can see the base_object_name shows the three part name.

If you have synonyms created without a database or schema name you can use this query to find them:

SELECT name, base_object_name 
FROM sys.synonyms
WHERE base_object_name NOT LIKE '%.%' -- one part name, no schema name, no database name
 OR base_object_name  LIKE '%..%' -- no schema name
 OR (base_object_name  NOT LIKE '%' + DB_NAME() +'%'
  AND base_object_name  NOT LIKE '%..%') -- no database name
GO

You can use the query above as one of your database checks or use this query as part of Policy Based Management.

This is the best practice we follow, but it does not mean that it's best for every scenario. If you only have synonyms for objects in the same database you may consider two-part names only.

Create a SSMS Synonym Template

Below is a SSSMS template that can be used to create a synonym with a three-part base object name. This will also check that the base object exists prior to creation. 

--==========================================-- Create Synonym with Check (Template)
--==========================================
IF (SELECT OBJECT_ID('<database_name, sysname, AdventureWorks>.<schema_name, sysname, Production>.<object_name, sysname, Product>')) IS NOT NULL
BEGIN
CREATE SYNONYM <synonym_schema_name, sysname, dbo>.<synonym_name, sysname, sample_synonym>
  FOR <database_name, sysname, AdventureWorks>.<schema_name, sysname, Production>.<object_name, sysname, Product>
PRINT 'Synonym <synonym_name, sysname, sample_synonym> for object <database_name, sysname, AdventureWorks>.<schema_name, sysname, Production>.<object_name, sysname, Product> Created'
END
ELSE PRINT 'Can not create Synonym for non-existing object'
GO

To use the template, copy this code into a query window and type Ctrl+Shift+M to execute.  A window will pop-up like the following where you can change the values to meet your needs and press OK to create the object.

Next Steps

• Read more tips about synonyms.
• If you are not familiar with SSMS Templates find out how to use them here.
• Learn more about SSMS Template Explorer here.
• Add checks to find synonyms that do not have three-part names to your scripts or to the Policy Based Management.

About the author

Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips