Reference to servers in queries

Question

Is there a way to alias a database in the same way that we can for tables? We have a set of servers, and when we create a test version of the system, all queries need their connections to databases updating. I am wondering if there's a better way to store database names so that procedures don't need to be re-written when we take a copy of the databases to make new test environments. For example by creating a function to return the right database names and servers for that specific environment. Then we only need to update one function per environment rather than updating all queries that use linked servers.

Answer 1

One way to address the problem is by using synonyms. Instead of referencing external objects using 3 or 4-part names in stored procedures, create a synonym for each external object and use those synonyms in queries. This way, you only need to recreate the synonyms after copying to a different environment. The procedures don't need to be changed.

Answer 2

Unless you are doing cross-database queries, you should not list the database name in your procedures. Then you would not need to do anything.

However, if you are doing cross-database queries, you can use SSDT variables to control the target database and redeploy the proc with the proper database names for your new environment.