Synonyms :
A Synonyms is a database object that serves the following purposes:- It provides an alternative name for another database object, referred to as the base object, that can exist on a local or remote server.
- It provies a layer of abstraction that protects a client application from changes made to the name or location of the base object.
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; |