Synonyms were first introduced in SQL 2005 and prove to be a great feature so far. What they basically do is decouple the database you are working in from other databases or objects you reference. This helps because it allows you to move objects (tables, procedures) to other locations without having to change the existing objects that reference them.
I used this recently in a project where I implemented a new system within an existing system. From a purist standpoint, the system should be on it’s own, but since there was no time to create subscriptions, we implemented it where most of the data was. While I did not like this method, I still to reference the data that should be external via synonyms.
To create a synonym, you can either use the dialog, or code. The dialog is pretty intuitive. Synonyms are created within a database, so expand the database and right click on Synonyms.
Once you do that, a dialog pops up:
|Synonym Name||Akin to the table or procedure name|
|Synonym Schema||Akin to the schema|
|Server Name||Server to reference, blank for current|
|Database name||Database to reference|
|Schema||external schema to reference|
|Object Type||[Drop down values]|
|Object Name||[Drop down values]|
And the code to create a synonym is:
Reference it in your code the same way you do a table:
*Intellisense doesn’t work with it as of this writing.
To see what synonyms are defined for the database, you can query the following system view: