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 :

?

1 2 3 4 5

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.

?

1 2 3 4 5 6 7 8 9 10 11 12

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;