6

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?

9

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 ;
| improve this answer | |
  • Expand it if you want to get any credit on your answer :-). – Marian Nov 21 '12 at 12:53
3

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.

| improve this answer | |
  • Umm, you're right. I missed the part with the view. My bad! Interesting tool, btw! – Marian Nov 21 '12 at 13:24
1

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;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.