I've been trying to figure out whether it's possible to have a Wildcard DB name in a SELECT query.
eg. Database1, Database2, Database3, Database4, Database5, Database6 all have the same schema - but for different aspects of the business.
What I would like to figure out to do is to run something like this:
SELECT Col1, Col2, Col3, Col4, Col5 FROM AllDatabases (where AllDatabases is a concatenation of Database1-6)
At the moment, the best solution i've come up with is to loop round all the DB's and place the results into a temporary table, then select the results from the temporary table.
HOWEVER - the list of DB's *could* change. Not often, but it's possible. Ideally it needs to be as dynamic as possible.
Am I barking up the wrong tree here, or should I just stick with the looped concatenation of the DB's ?