Home » SQL Server Synonyms

SQL Server Synonyms

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 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

 




Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.