Imagine a situation where a database driven application requires that SQL Server stored procedures be moved between development, stage, and production environments. This seems pretty common, right? Just script up the stored procedures and run the script in each environment. Done.
But what if the stored procedures access multiple databases? And what if the database names are not consistent across environments? Although you could find and replace the database names in the stored procedure create script you'd rather not have to. Ideally, the stored procedure create script would be portable and not specifically reference databases or tables directly.
Enter SQL Server 2005 Synonyms. As described in SQL Server 2005 Books Online:
A synonym is an alternative name for a schema-scoped object. Client applications can use a single-part name to reference a base oject by using a synonym instead of a two-part, three-part, or four-part name to reference the base object.
A SQL Server 2005 Synonym is an alias for a database object such as a table -- a way to introduce abstraction into a SQL Server stored procedure that increase its portability.
In essence, stored procedures reference synonyms and the synonym definitions tell SQL Server what specific objects the stored procedure is trying to use. In other words, references in a stored procedure stay the same and it is the synonym definitions that differ between environments.
The trick is to create a set of stored procedures that can easily recreate the synonyms for each environment (development, stage, production) based on what differs between those environment.
In a recent case, a database name differed between one of our client's environments. In addition, a target ERP application/database did something a little different -- the ERP system used a single SQL Server database to house multiple location-based (US, UK, etc) "databases" distinguished by a naming prefix, As a result, the resulting synonym creation stored procedure we created expected a database name and object prefix name to create the synonyms for each of this client's environments.