How to join table from one database with view from another database in SQL Server

Question

I have a table called TableA in database A and view ViewB in database B

I am trying to simply join the table with the view to write a query. For example:

SELECT * 
FROM TABLEA a, B.VIEWB b 
WHERE a.id = b.id

However, it is not working.

What is the way to join a table and a view from different databases? Should I be creating a synonym?....

Mitch Wheat · Accepted Answer · 2013-04-02 23:36:51Z

2

You can use three part naming or create a Synonym.

3-part naming would be:

SELECT * 
FROM DatabaseA.Schema.TABLEA a
join DatabaseB.Schema.VIEWB b ON a.id = b.id

Creating a synonym:

CREATE SYNONYM DatabaseB_ViewB
FOR DatabaseB.Schema.VIEWB;
GO

USE DatabaseA
GO

SELECT * 
FROM TABLEA a
join DatabaseB_ViewB b ON a.id = b.id

answered Apr 2, 2013 at 23:36

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

I think I would prefer the synonym approach. Should I be creating the synonym in DatabaseB?

Create the synonym in the database where its going to be used.