Merging two queries in bigquery

Question

I need to get table_schemna, table_name, creation_time and last modified time columns

using this query i am not getting table_name.

SELECT * FROM `region`.INFORMATION_SCHEMA.SCHEMATA;

using this query i am not getting Last modified time.

SELECT * FROM `region`.INFORMATION_SCHEMA.TABLES;

Is this possible to merge the above queries to get the required four columns or is there any other way to get the last_modified time ,creation time, table from multiple datasets

Answer 1

You can consider the below approach to get the table_id, creation time and last_modified time from datasets.

select table_id, TIMESTAMP_MILLIS(creation_time) AS creation_time,
TIMESTAMP_MILLIS(last_modified_time) AS last_modified_time,
dataset_id
FROM `project.dataset.__TABLES__`