SCHEMATA view

The INFORMATION_SCHEMA.SCHEMATA view provides information about the datasets in a project or region. The view returns one row for each dataset.

Before you begin

To query the SCHEMATA view for dataset metadata, you need the bigquery.datasets.get Identity and Access Management (IAM) permission at the project level.

Each of the following predefined IAM roles includes the permissions that you need in order to get the SCHEMATA view:

  • roles/bigquery.admin
  • roles/bigquery.dataEditor
  • roles/bigquery.dataOwner
  • roles/bigquery.dataViewer

For more information about BigQuery permissions, see Access control with IAM.

Schema

When you query the INFORMATION_SCHEMA.SCHEMATA view, the query results contain one row for each dataset in the specified project.

The INFORMATION_SCHEMA.SCHEMATA view has the following schema:

Column name Data type Value
CATALOG_NAME STRING The name of the project that contains the dataset
SCHEMA_NAME STRING The dataset's name also referred to as the datasetId
SCHEMA_OWNER STRING The value is always NULL
CREATION_TIME TIMESTAMP The dataset's creation time
LAST_MODIFIED_TIME TIMESTAMP The dataset's last modified time
LOCATION STRING The dataset's geographic location
DDL STRING The CREATE SCHEMA DDL statement that can be used to create the dataset
DEFAULT_COLLATION_NAME STRING The name of the default collation specification if it exists; otherwise, NULL.

Scope and syntax

Queries against this view must include a region qualifier. If you do not specify a regional qualifier, metadata is retrieved from the US region. The following table explains the region scope for this view:

View Name Resource scope Region scope
[PROJECT_ID.]INFORMATION_SCHEMA.SCHEMATA Project level US region
[PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.SCHEMATA Project level REGION
Replace the following:

  • Optional: PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.

  • REGION: any dataset region name. For example, `region-us`.

    • Example

    -- Returns metadata for datasets in a region.
SELECT * FROM region-us.INFORMATION_SCHEMA.SCHEMATA;

    Example

    To run the query against a project other than your default project, add the project ID to the dataset in the following format: 

    `PROJECT_ID`.INFORMATION_SCHEMA.SCHEMATA
    for example, `myproject`.INFORMATION_SCHEMA.SCHEMATA.

    SELECT
  * EXCEPT (schema_owner)
FROM
  INFORMATION_SCHEMA.SCHEMATA;

    The result is similar to the following. For readability, some columns are excluded from the result.

    +----------------+---------------+---------------------+---------------------+------------+------------------------------------------+
|  catalog_name  |  schema_name  |    creation_time    | last_modified_time  |  location  |                   ddl                    |
+----------------+---------------+---------------------+---------------------+------------+------------------------------------------+
| myproject      | mydataset1    | 2018-11-07 19:50:24 | 2018-11-07 19:50:24 | US         | CREATE SCHEMA `myproject.mydataset1`     |
|                |               |                     |                     |            | OPTIONS(                                 |
|                |               |                     |                     |            |   location="us"                          |
|                |               |                     |                     |            | );                                       |
+----------------+---------------+---------------------+---------------------+------------+------------------------------------------+
| myproject      | mydataset2    | 2018-07-16 04:24:22 | 2018-07-16 04:24:22 | US         | CREATE SCHEMA `myproject.mydataset2`     |
|                |               |                     |                     |            | OPTIONS(                                 |
|                |               |                     |                     |            |   default_partition_expiration_days=3.0, |
|                |               |                     |                     |            |   location="us"                          |
|                |               |                     |                     |            | );                                       |
+----------------+---------------+---------------------+---------------------+------------+------------------------------------------+
| myproject      | mydataset3    | 2018-02-07 21:08:45 | 2018-05-01 23:32:53 | US         | CREATE SCHEMA `myproject.mydataset3`     |
|                |               |                     |                     |            | OPTIONS(                                 |
|                |               |                     |                     |            |   description="My dataset",              |
|                |               |                     |                     |            |   location="us"                          |
|                |               |                     |                     |            | );                                       |
+----------------+---------------+---------------------+---------------------+------------+------------------------------------------+