SQL Query multiple databases

Question

I need to run a SELECT query on a SQL Server and return info pulled from multiple databases on the same server. I have the following, which could work:

SELECT [Name], [Nationality]
FROM [dbtest].[dbo].[Staff]
WHERE Nationality = 'Canadian'

Union all

SELECT [Name], [Nationality]
FROM [dbtest2].[dbo].[Staff]
WHERE Nationality = 'Canadian'

etc..

The problem is I have 2000 databases to query and it will almost certainly be added to in future, meaning the query will need editing.

What I really need is a wildcard for the database name.

ie:

*.[dbo].[Staff] 

but this gives me an error. Even if it worked, not all databases have the same tables, so is likely to error anyway.

Any way I can do this without typing out the above example 2000 times ?

Answer 1

You can build the statement dynamically

DECLARE @Query varchar(max) = ''

SELECT @Query = COALESCE(@Query + ' UNION ALL ', '') + 'SELECT [Name], [Nationality] FROM [' + TABLE_CATALOG+'].dbo.[Staff] WHERE Nationality = ''Canadian'''
FROM information_schema.tables 
WHERE table_name = 'Staff'

SET @Query = STUFF(@Query, CHARINDEX('UNION ALL', @Query), 10, '')

PRINT @Query

EXEC(@Query)

Answer 2

One way of doing that is to generate query on the fly. Either by querying information_schema DMV as in @Yosi answer or by using your own metadata table (in case tables can have different names in different databases or there are any other significant differences in table's schema) :

1. Create some master database and create table here which will contain list of all other databases - mainly DB name but you can include any other information describing differences (and use this data during query generation). You can add more (or delete) database "definitions" here.

2. Create stored procedure which will iterate master table and generate query on the fly

3. Execute query using sp_exec