Bigquery external table metadata

Question

I want to extract bigquery external table metadata. I've gone through documetation

But I'm not able to find the field which gives me the information related to the external table location on gcs. So is there any other metadata table that gives me location information of the external table?

Answer 1

Using SPLIT since there can be multiple comma separated uris:

SELECT ddl, SPLIT(REGEXP_EXTRACT(ddl, r"(?i)uris\s*=\s*\[(.*)\]")) as uris
FROM `catalog.schema.INFORMATION_SCHEMA.TABLES`
WHERE table_type = 'EXTERNAL';