Query across multiple databases on same server

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]

It's not going to be the cleanest solution ever, but you could define a view on a "Master database" (if your individual databases are not going to stay constant) that includes the data from the individual databases, and allows you to execute queries on a single source.

For example...

CREATE VIEW vCombinedRecords AS
SELECT * FROM DB1.dbo.MyTable
UNION ALL
SELECT * FROM DB2.dbo.MyTable

Which allows you to do...

SELECT * FROM vCombinedRecords WHERE....

When your databases change, you just update the view definition to include the new tables.

You can build the union dynamically:

select name from sys.databases

and then check if the database has the table:

select name from [dbname_from_above].sys.tables where name = 'YourTable'

That gives you all databases for the union. You can build the query client side or in dynamic SQL.