SQL | SYNONYM

A SYNONYM provides another name for database object, referred to as original object, that may exist on a local or another server. A synonym belongs to schema, name of synonym should be unique. A synonym cannot be original object for an additional synonym and synonym cannot refer to user-defined function. The query below results in an entry for each synonym in database. This query provides details about synonym metadata such as the name of synonym and name of the base object.

select * 
from sys.synonyms ;

Note : Synonyms are database dependent and cannot be accessed by other databases. Syntax -

CREATE SYNONYM synonymname 
FOR servername.databasename.schemaname.objectname;
GO

Example - Let us assume Geektabtable of GFGdatabase, Geeekshschema on server named Server1. To reference this table from another server, Server2, an application would have to use four-part named Server1.GFG.Geeeksh.Geektab. Also, if the location of table were to change, for example, to another server, application would have to be modified to reflect that change. To address both these issues, one can create synonym, Geektable, on Server2for Geektabtable on Server1. Now, the application only has to use single-part name, Geektable, to point Geektab table. Also, if location of the Geektab table changes, you will have to modify synonym, Geektable, to point to new location of Geektab table. Now, let us create synonym for Geektab table of GFG database, Geeeksh schema on server named Server1.

CREATE SYNONYM Geektable  
FOR Server1.GFG.Geeeksh.Geektab;  
GO

Find the output in Server2 by using synonym.

SELECT ID, Name    
FROM Geektable;

Output -

ID	Name
1	Nisha
2	Mira
3	Punit
4	Ram

(4 row(s) affected)