Synonyms in SQL Server 2005

from: http://blog.csdn.net/itblog/article/details/752881

============= Creating synonyms

You can create synonyms for the following object types:

Assembly (CLR) stored procedures	Assembly (CLR) table-valued functions
Assembly (CLR) scalar functions	Program Clustering (CLR) aggregation functions
Replication filtering process	Extended stored Procedures
SQL scalar functions	SQL table-valued functions
SQL Inline table-valued functions	SQL Stored Procedures
View	Table 1 (User defined)

1 including local and global temporary tables

Using the four-part name of a function base object is not supported.

Synonyms can be created, deleted, and referenced in dynamic SQL.

After you do not reference or do not need synonyms, you can delete synonyms.

--a: Creating synonyms for local objects
==================================================================
CREATE synonym Mybom
For TEST. Dbo. BOM;
GO
SELECT *
From Mybom
WHERE ID < 5;
GO

--b: Creating Synonyms for remote objects
==================================================================
--Create a linked server
exec sp_addlinkedserver ' srv_lnk ', ' ', ' SQLOLEDB ', ' Remote server name or IP address '
exec sp_addlinkedsrvlogin ' Srv_lnk ', ' false ', NULL, ' username ', ' password '
GO
Use DatabaseName
CREATE synonym with the word for srv_lnk. remote database. dbo. remote table;
GO
SELECT * from the same word

--Remove linked server when no longer in use
exec sp_dropserver ' srv_lnk ', ' droplogins '

======== Deleting synonyms ===================================================
References to synonyms are not schema-bound, so synonyms can be removed at any time. A reference to a deleted synonym can only be found at run time. Synonyms can be created, deleted, and referenced in dynamic SQL.

DROP synonym synonym Name

---------------------->>>