Take a look at this other tip about SYNONYMs: How and why should I use SQL Server 2005 synonyms? Sometimes a simple alias may be the best choice instead of a lot of re-work to move or rename objects. Now that you know that you can create a SYNONYM to reference objects in an existing database or another database look for ways that a SYNONYM may be used. SYNONYM can not be referenced in a DDL statement.spelling error, the synonym will created, but you will get an error while accessing the object. So this means that if you make any related error e.g. The object for which the SYNONYM is being created is checked at run time.Obviously consumes possible object names, as you can not create a table with the same name of a synonym.It means that you can not create SYNONYM of a SYNONYM. So you can delete a SYNONYM without getting any warning that it is being referenced by any other database object. SYNONYMs are loosely bound to the referenced objects.SYNONYMs can be useful if you give the front-end query tools like spreadsheets and Access linked tables direct links in to the tables.SYNONYMs can be created in the same database to provide backward compatibility for older applications in case of drop or rename of objects.Provides flexibility for changing the location of objects without changing existing code.Allow changes to complicated (multi part) and lengthy names with a simplified alias as a same server resident object.SYNONYMs provide a layer of abstraction over the referenced object.Assembly Stored Procedures, Table Valued Functions, Aggregations.SYNONYM's can be very useful and can be created for To get the meta data for all synonyms use the following command SELECT * FROM sysobjectsĪnd to drop the synonym use the following command USE AdventureWorks Here is an example to create the SYNONYM in one database that references an object in another database. To check that this works you can issue a query such as below that uses the new SYNONYM. USE AdventureWorksĬREATE SYNONYM MySyn FOR Production.Location Here is an example to create the SYNONYM in a database that references another object in the database. Also, you create a synonym that crosses databases you will need permissions for the other database as well.

If you are a sysadmin or db_owner you will have these privileges or you can grant this permission to other users. In order to create a synonym, you need to have CREATE SYNONYM permissions. It is just a pointer or reference, so it is not considered to be an object. It is a way to give an alias to an already existing or potential new object. The feature is SYNONYMs in SQL Server 2005. Fortunately I found a feature in SQL Server 2005 which solved my problems and provided further optimization and facilities.

There are synonym