Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers 3

up vote 6 down vote accepted

It's, what, 5 minutes later and 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
share|improve this answer
    
Expand it if you want to get any credit on your answer :-). –  Marian Nov 21 '12 at 12:53
add comment

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.

share|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
add comment

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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