Dynamic database reference in T-SQL scripts

Question

We have a set of databases (development, QA and production, dev-foo & dev-bar, qa-foo & qa-bar and just foo & bar respectively).

Inside foo family of databases there's a view that should use respective bar database to do cross-database joins. In other words,

select f.*, b.* from foo f inner join <bar-db>.dbo.bar b on f.barid = b.id

The question is: is there any way I could write this script once and then have a database-level variable <bar-db> that would point to an appropriate "instance" of a bar database?

Answer 1

The answer is Synonyms.

Repeat for each database in foo family:

create synonym bar for [srv-db].<bar-db>.dbo.bar;

And then use it as follows:

select f.*, b.* 
from foo f inner join bar b on f.barid = b.id ;

Answer 2

Synonyms and dynamic SQL are both solid answers (although you can't use dynamic SQL within a view). If you are using Visual Studio to manage your database projects, there is another route open to you: database variables. If you have database projects named foo and bar and configurations Debug, QA, and Release, you could use an app.config transform (perhaps using a tool like Slow Cheetah) and on deployment, the correct database reference would be used and a later DBA wouldn't get confused as to what "foo" means in that context. Synonyms aren't very commonly used in my experience, so they are something that one could easily overlook.

Answer 3

I's say this is better fitted with dynamic sql. Something like:

declare
    @sql nvarchar(max),
    @database sysname = 'FooDB';

select @sql = N'select f.*, b.* from foo f inner join [' + @database + '].dbo.bar b on f.barid = b.id';
exec sp_executesql @sql;