Search code examples
sqlgoogle-bigqueryinformation-schema

BigQuery: query multi schemas for a specific table

Schema tables:

 1. AA
 2. AB
 3. BB
 4. BA
 5. ...

Target: search for a table that can be in any of the schemas?

Desired query:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'xyz' and schema_name LIKE '%A'

Works but need to repeat manually for all the schemas:

SELECT * FROM AA.INFORMATION_SCHEMA.TABLES WHERE table_name = 'xyz'
Solution

  • You should use region qualifier - in this case you will get ALL tables in respective region vs. tables in just default or specified dataset (if you use FROM INFORMATION_SCHEMA.TABLES or FROM myDataset.INFORMATION_SCHEMA.TABLES respectively)

    So, to your version of "desired query" you should just simply add region name like in below example (also note use of table_schema field vs. schema_name)

    SELECT * 
FROM region-us.INFORMATION_SCHEMA.TABLES 
WHERE table_name = 'xyz' 
AND table_schema LIKE '%A'

    Please note: you need to make sure you have proper permissions set to view metadata. For example, I've added BigQuery Metadata Viewer Role for this to work