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

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;
-- Create a synonym for the Product table in AdventureWorks.

  CREATE SYNONYM MyProduct FOR AdventureWorks.Production.Product;

-- Query the Product table by using the synonym.

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


Post a Comment