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

SQL Server Unpivot

SQL Server ROLLUP

SQL Server Cube

SQL Server Grouping Sets

SQL User Defined Functions

SQL Server Table Type and Table Valued Parameters

 853 total views,  2 views today

Leave a Reply

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