SQL Server : Synonyms

Synonyms :

A Synonyms is a database object that serves the following purposes:

1. It provides an alternative name for another database object, referred to as the base object, that can exist on a local or remote server.
2. It provies a layer of abstraction that protects a client application from changes made to the name or location of the base object.

Synonyms can be created for the following types of objects:

Assembly (CLR) Stored Procedure	Assembly (CLR) Table_valued Function
Assembly (CLR) Scalar Function	Assembly Aggregate (CLR) Aggregate Function
Replication-filter-procedure	Extended Stored Procedure
SQL Scalar Function	SQL Table-Valued Function
SQL Inline table valued Function	SQL Stored Procedure
View	Table (User-Defined)

Creating a Synonyms :

Syntax :

  CREATE SYNONYM [schema_name_1.] sunonum_name FOr < object . < object > :: =
    {
       [ server_name. [ databse_name ] . [ schema_name_2 ] . | database_name . [ schema_name_2 ] . | schema_name_2. ]
      object_name
    }
    

Creating a Synonyms for a Local Object :

The following example first creates a synonyms for the base object, Product in the AdventureWorks database, and then queries the synonym.

 USE tempdb;
  GO
-- Create a synonym for the Product table in AdventureWorks.

  CREATE SYNONYM MyProduct FOR AdventureWorks.Production.Product;
  GO 

-- Query the Product table by using the synonym.

 USE tempdb;
  GO
  SELECT ProductID, Name FROM MyProduct WHERE ProductID < 5;