INFORMATION_SCHEMA in BigQuery

The INFORMATION_SCHEMA.TABLES view contains one row for each table or view in a dataset.

It provides metadata such as table name, creation time, and the DDL statement used to recreate the table.

Syntax

[PROJECT_ID.]DATASET_ID.INFORMATION_SCHEMA.TABLES    

Schema Overview

The INFORMATION_SCHEMA.TABLES view includes the following important columns:

• table_catalog (STRING): Project ID that contains the dataset.
• table_schema (STRING): Dataset name containing the table or view.
• table_name (STRING): Name of the table or view.
• ddl (STRING): DDL statement used to recreate the table or view.
• creation_time (TIMESTAMP): Table creation timestamp.
• and many other metadata columns.

Example: Retrieve metadata for all tables and views in a dataset.

SQL

SELECT *
FROM `ashishcoder.Coding_Dataset.INFORMATION_SCHEMA.TABLES`;    

The output of the above code is shown below: