1DECLARE dataset_names ARRAY<STRING>;
2
3SET dataset_names = (
4 SELECT ARRAY_AGG(SCHEMA_NAME) FROM `region-us.INFORMATION_SCHEMA.SCHEMATA`
5);
6
7EXECUTE IMMEDIATE (
8 SELECT
9 STRING_AGG(
10 (SELECT """
11 SELECT project_id, dataset_id, table_id, row_count, size_bytes
12 FROM `""" || s || """.__TABLES__`"""
13 ),
14 " UNION ALL ")
15 FROM UNNEST(dataset_names) AS s
16);
17