Query with multiple databases

Question

I was just wondering could I write a query that fetches data from two different databases?

Answer 1

Hi，@Jonathan Brotto
The key point is that whether the two databases are on the same server.
1.Two databases on the same server
If you have access to both databases, and your table keys match, you can join across databases by specifying the fully qualified name :[Database].[Schema].[Table]
for example,

SELECT T1.ID, T1.name,T2.ID,T2.name  
FROM DatabaseA.dbo.Table1 T1  
JOIN DatabaseB.dbo.Table2 T2   
on T2.ID = T1.ID  

2.Two databases on different servers :

exec sp_addlinkedserver 'alias','','sqloledb','ip address'  
exec sp_addlinkedsvlogin 'alias','false',null,'uesername','password'  
go  
SELECT ...  
FROM alias.DatabaseA.dbo.Table1 T1  
JOIN alias.DatabaseB.dbo.Table2 T2   
on ...  

Note:Two servers can access the database only if they are allowed to communicate

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Yes, for example the following query gets all databases in the master database and all the tables in the msdb database

Select 'master', name From master.sys.databases
Union All
Select 'msdb', name From msdb.sys.tables;

Tom