A synonym is a database object that allows an alternative name for another database object, referred to as the base object, that can exist on a local or remote server.
SYNTAX
CREATE SYNONYM [ schema_name_1. ] synonym_name FOR object; Object : [ server_name.[ database_name ] . [ schema_name_2 ]. object_nameย ย
Lets see an example of using synonyms in SQL.
Here we have a table named PersonPhone within a schema Person as shown below.
Select * from Person.PersonPhone
Creating a Synonyms for a table within the same database
Lets create a synonyms for a table Person.PersonPhone as shown below.
CREATE SYNONYM tblPhone FOR Person.PersonPhone
As you have created a synonyms for target object that is table Person.PersonPhone, now you can reference this table by using synonyms as following.
SELECT * FROM tblPhone
As you can see, Instead of writing full name Person.PersonPhone to access the table you can just access the table by synonyms tblPhone.
Creating a Synonyms for a table within another database
Lets take a database SqlTutorials and within this database, will access a table from another database as shown below.
Use SqlTutorials ; Go SELECT* FROM AdventureWorks2017.Person.PersonPhone
Now we will create a synonyms for target object that is a table which exists in another database (AdventureWorks2017.Person.PersonPhone).
Use SqlTutorials ; Go CREATE SYNONYM tblPhone FOR AdventureWorks2017.Person.PersonPhone
Lets access a table from another database using synonyms as following.
Use SqlTutorials ; Go SELECT * FROM tblPhone
You can also check all synonym which are created on current database either using T-SQL query or object explorer.
Using T-SQL Query :
SELECT name, object_Id,type_desc,create_date,base_object_name FROM sys.synonyms
Using Object Explorer:
Go to object Explorer > Click on Database > Click on Synonyms Folder
Also Read..
SQL Server Table Type and Table Valued Parameters
