Synonyms can be used to simplify code when doing cross-database work. They are especially helpful for standardizing code when the external database name varies from environment to environment. Consider these databases:
* MyDB_dev
* MyDB_prod
* YourDB_dev
* YourDB_prod
Besides the environment-specific databases, this would work where there is a set of databases (MyDB and YourDB) for each project or client.
MyApplication connects to either MyDB_dev or MyDB_prod. Code in MyDB (or MyApplication) needs to use YourObject in YourDB. Rather than using messy/repetitive/dynamic code to access either YourDB_dev.YourObject or YourDB_dev.YourObject , we encapsulate the database name differences in synonyms.
On MyDB_dev: CREATE SYNONYM dbo.YourObject FOR YourDB_dev.dbo.YourObject;
On MyDB_prod: CREATE SYNONYM dbo.YourObject FOR YourDB_prod.dbo.YourObject;
Once the synonyms are set up (correctly), all code is instance-agnostic, and there is no risk of getting dev data in prod.