What is Synonym?

In SQL Server, a synonym is an alias for another database object, such as a table, view, stored procedure, user defined function, and so on. Synonyms provide a way to standardize object references across databases and to provide alternative names for database objects. SQL Server synonyms can be created for both system and user-defined objects. In this article, we will show you how to add and remove a synonym in SQL Server database using T-SQL commands and Microsoft Visual Studio tool.

T-SQL add synonym

You can add a synonym in a SQL Server database using the following T-SQL syntax:
CREATE SYNONYM Synonym_name
FOR object_name
GO

CREATE SYNONYM [dbo].[Synonym_name]
FOR [schema_name].[object_name]
GO

T-SQL synonym example

CREATE SYNONYM MySynonym
FOR MyTable
GO
In the above example, we have created a SQL Server synonym named MySynonym for the MyTable table. This way, we can reference the table using the synonym name MySynonym. To create a SQL Server synonym, you must have CREATE SYNONYM permission in the database. By default, members of the sysadmin fixed server role and db_owner fixed database role can create synonyms.

Add Synonym with Microsoft Visual Studio

Let us see how to create a SQL Server synonym using Microsoft Visual Studio.
Microsoft Visual Studio:
Please follow the below steps to create a SQL Server synonym using Microsoft Visual Studio.
1) First, right-click on the Synonyms folder and choose the New Synonym menu item. This will open the Add New Synonym dialog box.
2) Enter the name of the synonym after the statement CREATE SYNONYM.
3) After the FOR keyword enter the name of the schema and the object for which you want to add the synonym.
4) Click Update button to add SQL Server synonym. This will open the Preview Database Updates dialog box.
5) Click Update Database button to complete the add synonym operation.

Use synonym

Let us now see how to use this SQL Server synonym.
CREATE SYNONYM cust_synonym
FOR customers
GO
SELECT * FROM cust_synonym;
In the example above, in the SELECT statement we replaced the table name with the synonym created for that table. The result of the query must be exactly the same as when no synonym is used.

Drop synonym

The DROP SYNONYM statement is used to delete a SQL Server synonym.
DROP SYNONYM synonym_name;
The following example shows how to delete the existing synonym from the SQL Server database.
DROP SYNONYM cust_synonym;