Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Synonym is to give alias (an alternative name) to some objects on a database basis. The synonym can be created for following objects.
- Table
- View
- Stored Procedure
- Function
After creating Synonym, you can access the original object via synonym.
So why do we need synonym?
Consider that there are many different code written in the form of Server1.Database.Schema.Table to access a table in the application.
If you move the database from Server1 to Server2 or move the table to another schema, this code block will not work.
If schemas or servers of objects(such as Table, View, Stored Procedure, and Function) change, you can only resolve the problem without modifying the code by deleting synonym and recreating it in the same name to point to the new location of the table.
Create Synonym:
Right click on Synonyms under the database and click New Synonym…
We then make the necessary choices as in the following table:
| Synonym name: | We give a name to the synonym |
| Synonym schema: | We’re choosing the schema where we create Synonym. |
| ServerName: | The Server where the object is located. |
| Database name: | The Database where the object is located. |
| Schema: | The Schema where the object is located. |
| Object type: | Object Type(Tablo,View,Stored Procedure or Function) |
| Object name: | We’re choosing the object |
Then go to the Permissions tab, click Search and select a user previously maped to the database and give the necessary privileges to the user.
You can read my article “How To Create a Login On SQL Server(Manage Logins)” to learn how to map a Login to a database.
Once you’ve created Synonym, you can query it as follows.
1 | Select * FROM dbo.MySynonym |
