BigQuery select last modified time using __TABLES__ from all tables in all datasets?

Question

I would like to get list or csv and store it in GCS, where the list contains list of all the tables and the last modified date.

I have more than 130 datasets and 10-20 tables in each datasets. Since I will be running this query everyday using scheduled github actions to compare and check which tables are modified after 2 months gap and send alert notification everyday to my team, I am trying to find a simple solution to get fetch this information from BQ.

I have got the query for single dataset:

SELECT 
   table_id,
   DATE(TIMESTAMP_MILLIS(last_modified_time)) AS last_modified
FROM
   `project_id.dataset_name.__TABLES__`

and to find all datasets I can use query :

SELECT
    schema_name
FROM
    `project_id`.`region-europe-west3`.INFORMATION_SCHEMA.SCHEMATA;

Answer 1

In general, this is called dynamic SQL and it can be done in GBQ with the execute immediate command.

execute immediate (
  select
    string_agg(
      concat(
        'select ',
        chr(39),
        schema_name,
        chr(39),
        ' as schema_name, table_id, date(timestamp_millis(last_modified_time)) as last_modified from project-id.', 
        schema_name,
        '.__TABLES__'
      ),
      ' union all '
    ) as query
  from `project-id`.INFORMATION_SCHEMA.SCHEMATA
)
;