Tell me more ×
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.
up vote 2 down vote favorite

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

up vote 3 down vote accepted

Just use 3 part object names

SELECT 
   A.col1, B.col2
FROM
   DatabaseA.dbo.TableA A
   JOIN
   DatabaseA.dbo.TableB B ON A.xx = B.xx

But you can create DatabaseC if you want but it adds no value

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
1  
@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

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.