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.
CREATE SYNONYM [ schema_name_1. ] synonym_name FOR object; Object : [ server_name.[ database_name ] . [ schema_name_2 ]. object_name
Lets create an example of using the create synonyms statement in Sql Server.
Creating a Synonyms for a table within the same database
Here we have a table PersonPhone within a schema Person as given.
Select * from Person.PersonPhone
Now we create a synonyms for the table – Person.PersonPhone.
CREATE SYNONYM tblPhone FOR Person.PersonPhone
As we have created a synonyms for target object that is table Person.PersonPhone, now we can reference this table by using synonyms as following
SELECT * FROM tblPhone
Creating a Synonyms for a table within another database
Now we are using database SqlTutorials, With in this database we access a table from another database.
Use SqlTutorials ; Go SELECT* FROM AdventureWorks2017.Person.PersonPhone
Now we 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
Now we can access a table from another database using synonyms as following
Use SqlTutorials ; Go SELECT * FROM tblPhone
We 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