SQL Query multiple databases
I need to run a query SELECT
on SQL Server and return information from multiple databases on one server. I have the following that might 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 that I have 2000 databases to query and this will almost certainly be added in the future, meaning the query will need to be edited.
What I really need is a wildcard for the database name.
those.:
*.[dbo].[Staff]
but that gives me an error. Even if it works, not all databases have the same tables, so there will probably still be errors.
Is there anyway I can do this without typing the above example 2000 times?
source to share
You can dynamically build a statement
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)
source to share
One way to do this is to generate a request on the fly. Either by querying the information_schema
DMV as in @ Yosi's answer, or using a custom metadata table (in case the tables might have different names in different databases or there are other significant differences in the table schema):
-
Create a main database and create a table here that will contain a list of all other databases - basically the name of the database, but you can include any other information describing the differences (and use that data while generating your query). Here you can add additional (or remove) database "definitions".
-
Create a stored procedure that will iterate over the main table and generate a query on the fly
-
Executing a query using
sp_exec
source to share