Query across multiple databases on same server

Question

I am looking for a way of dealing with the following situation:

1. We have a database server with multiple databases on it (all have the same schema, different data).

2. We are looking for a way to query across all the databases (and for it to be easy to configure, as more databases may be added at any time). This data access must be realtime.

Say, as an example, you have an application that inserts orders - each application has its own DB etc. What we are then looking for is an efficient way for a single application to then access the order information in all the other databases in order to query it and subsequently action it.

My searches to date have not revealed very much, however I think I may just be missing the appropriate keywords in order to find the correct info...

Answer 1

You must specify the database name before any database object.

Single database:

SELECT * FROM [dbo].[myTable] 

Multiple dabases:

SELECT * FROM [DB01].[dbo].[myTable] UNION ALL SELECT * FROM [DB02].[dbo].[myTable] UNION ALL SELECT * FROM [DB03].[dbo].[myTable]