Using wildcard in bigquery project name

I am trying to extract some information from all tables in the SALES dataset. I want to run it for multiple clients. Each client has a big query project. Bellow query returns what I want for our London client. How I can use a wildcard to do the same query for the other 10 clients and union all in one table?

SELECT *
FROM   london_prod_uk_eu.sales.__tables__ 

Basically, I want to simplify the below query:

SELECT *
FROM   london_prod_uk_eu.sales.__tables__
UNION ALL
SELECT *
FROM   toronto_prod_can_us.sales.__tables__

SELECT *
FROM   rome_prod_it_eu.sales.__tables__
UNION ALL
SELECT *
FROM   madrid_prod_sp_eu.sales.__tables__ 

Answer

Consider below approach

declare query array<string> default [];
declare projects array<string>;
set projects = [
  'london-prod-uk-eu',
  'toronto-prod-ca-us',
  'rome-prod-it-eu',
  'madrid-prod-sp-eu'
];

for record IN (select project from unnest(projects) project) do
   set query =  query || [format('select * from `%s.SALES.__TABLES__`', record.project)];
end for;

execute immediate (select string_agg(line, ' union all ') from unnest(query) line);