“In SQL Server, a synonym refers to an alias or a substitute name assigned to an existing database object such as a table, a view, a stored procedure, a UDF, or a sequence. Synonyms play an important role depending on the functionality and the method in which they are implemented.

In this post, we will walk you through using the CREATE SYNONYM statement in SQL Server to assign secondary names to database objects.”

Statement Syntax

The following is the syntax of the CREATE SYNONYM statement:

The statement accepts the following arguments:

1. schema_name_1 – this defines the name of the schema in which the synonym resides. SQL Server will create the synonym in the default schema if the schema is not specified.
2. synonym_name – the name of the synonym.
3. server_name – the name of the server on which the base object is stored.
4. database_name – sets the name of the database on which the target object resides. If not specified, the currently selected database is used.
5. schema_name_2 – refers to the schema name where the object resides.
6. object_name – sets the name of the object referenced by the synonym.

Create a Synonym for Table

The following example depicts the use of the CREATE SYNONYM command to create an alias for a table in the same database.

create synonym databases for dbo.resolver.entries;

We can then query the table using the create alias as:

Output:

SQL Server Show Synonyms

To show existing synonyms using a Transact-SQL command, we can run the following:

Output:

You can also view the available synonyms in SQL Server Management Studio by browsing the object explorer.

To delete a synonym, use the DROP SYNONYM command as:

Example:

drop synonym if exists dbo.databases;

Termination

This post taught you how to use the CREATE SYNONYM command to create an alternative for database objects in SQL Server.