sql server - SQL Query multiple databases -

i need run select query on sql server , return info pulled multiple databases on same server. have following, work:

select [name], [nationality] [dbtest].[dbo].[staff] nationality = 'canadian'  union  select [name], [nationality] [dbtest2].[dbo].[staff] nationality = 'canadian'  etc.. 

the problem have 2000 databases query , added in future, meaning query need editing.

what need wildcard database name.

ie:

*.[dbo].[staff]  

but gives me error. if worked, not databases have same tables, error anyway.

any way can without typing out above example 2000 times ?

you can build statement dynamically

declare @query varchar(max) = ''  select @query = coalesce(@query + ' union ', '') + 'select [name], [nationality] [' + table_catalog+'].dbo.[staff] nationality = ''canadian''' information_schema.tables  table_name = 'staff'  set @query = stuff(@query, charindex('union all', @query), 10, '')  print @query  exec(@query)