Changing Database Names and Cross Database Queries In Stored Procedures

Question

I have a number of related databases that are version-ed. Instance of different versions may run side by side, identified by their different versions, i.e. [NorhwindV1.1] and [NorhwindV1.2] may be on the same server, along with [SouthwindV1.1] and [SouthwindV1.2].

There are a number of stored procedures that make cross database queries, i.e. exist on NorthwindV1.1 and also query tables in SouthwindV1.1. What is the best way to manage the change in database names with changing version number, so that the stored procedures query the correct version of the databases?

Thanks, MagicAndi.

Answer 1

Assuming the number of tables queried between the databases is manageable, you could create synonyms for those tables and use the synonym in the procedures. You would then just need to change the synonyms to switch between versions.

http://msdn.microsoft.com/en-us/library/ms177544.aspx