1. existing in another server.
2. which exist in another database within the same server.
3. which exist in another schema.
Let us see a basic example of these here:
Example for point 1:
Create Synonym testSynonym for [YourServerName].YourDatabaseName.dbo.YourTableName
Select Field1, Field2 from testSynonym;
Example for point 2:
Create Synonym testSynonym_AnotherDatabase For Northwind.dbo.categories
I haven't checked any sample for point 3 yet.
In the above example I have used the table "Categories" from Northwind database. Lets assume that I use a table name which doesn't exist at all (say, "tblVadivel") still the Synonym would be created !!!
Confused :) It works like this ... "the actual check of whether the object we used exists or not is happening on the runtime and not on the design time". Hope I have made the point clear!
Objects which can be used while creating a Synonyms are listed below for the sake of completion:
3. Stored Procedure
4. User Defined Function
5. # and ## tables
Drop Synonym Name_Of_Your_Synonym
Technorati tags: SQL, Databases, SQL Server, SQL Server 2005