Cross-database queries with different DB names in different environments?

Question

How would you handle cross database queries in different environments. For example, db1-development and db2-development, db1-production and db2-production.

If I want to do a cross-database query in development from db2 to db1 I could use the fully qualified name, [db1-development].[schema].[table]. But how do I maintain the queries and stored procedures between the different environments? [db1-development].[schema].[table] will not work in production because the database names are different.

I can see search and replace as a possible solution but I am hoping there is a more elegant way to solve this problem. If there are db specific solutions, I am using SQL Server 2005.

Answer 1

Why are the database names different between dev and prod? It'd, obviously, be easiest if they were the same.

If it's a single table shared, then you could create a view over it - which only requires that you change that view when moving to production.

Otherwise, you'll want to create a SYNONYM for the objects, and make sure to always reference that. You'll still need to change the SYNONYM creation scripts, but that can be done in a build script fairly easily, I think.

Answer 2

For this reason, it's not practical to use different names for development and production databases. Using the same db name on development, production, and optionally, acceptance/Q&A environments, makes your SQL code much easier to maintain.

However, if you really have to, you could get creative with views and dynamic SQL. For example, you put the actual data retrieval query inside a view, and then you select like this:

declare @environment varchar(10)
set @environment = 'db-dev' -- input parameter, comes from app layer

declare @sql varchar(8000)
set @sql = 'select * from ' + @environment + '.dbo.view'
execute(@sql)

But it's far from pretty...