BigQuery で 180 日間参照されていないテーブルを出すクエリ

tables_that_have_not_been_referenced_for_180_days.sql

  EXECUTE IMMEDIATE(
  SELECT
    FORMAT("""
    WITH
      referenced_tables AS(
      SELECT
        DISTINCT referenced_table.project_id,
        dataset_id,
        table_id
      FROM
        `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
      JOIN
        UNNEST(referenced_tables)referenced_table),
      tables AS(%s)
    SELECT
      table_catalog,
      table_schema,
      table_name
    FROM
      tables
    LEFT JOIN
      referenced_tables
    ON
      project_id=table_catalog
      AND dataset_id=table_schema
      AND table_id=table_name
    WHERE
      project_id IS NULL
      """,STRING_AGG(FORMAT("""
      SELECT
        table_catalog,
        table_schema,
        table_name
      FROM
        `%s`.INFORMATION_SCHEMA.TABLES""",schema_name),"""
      UNION ALL"""))
  FROM
    INFORMATION_SCHEMA.SCHEMATA)