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.

Using SQL 2005 or 2008, is it possible to create a database called C that is a view into all tables in database A + B ? Databases A, B, and C are on the same local server.

I am wanting to be able to create join queries between tables that are in 2 different databases. Is there another way to do this?

share|improve this question

1 Answer 1

up vote 5 down vote accepted

You can create DatabaseC if you want to, but it adds no value. Just use 3 part object names:

SELECT 
   A.col1, B.col2
FROM
   DatabaseA.dbo.TableA A
   JOIN
   DatabaseB.dbo.TableB B ON A.xx = B.xx
share|improve this answer
2  
What if this was an indexed view? You could materialize it into database C if this fit some purpose? –  Tevo D Nov 22 '11 at 19:23
    
I can't believe I didn't think of that. I just didn't realize it was possible. It worked. Thanks! –  djangofan Nov 22 '11 at 19:29
2  
@TevoD - You can't SCHEMABIND to tables in other databases which is a requirement for indexed views. –  Martin Smith Nov 23 '11 at 8:47
1  
@MartinSmith to add some color to that: SQL Server can't maintain indexed view consistency across databases because they could be restored, copied and they are not crash consistent as a unit. –  usr May 22 at 20:40

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.