SCHEMATA view

TheINFORMATION_SCHEMA.SCHEMATA view provides information about the datasetsin a project or region. The view returns one row for each dataset.

Before you begin

To query theSCHEMATA view for dataset metadata, you need thebigquery.datasets.getIdentity and Access Management (IAM) permission at the project level.

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

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

For more information about BigQuery permissions, seeAccess control with IAM.

Schema

When you query theINFORMATION_SCHEMA.SCHEMATA view, the query results containone row for each dataset in the specified project.

TheINFORMATION_SCHEMA.SCHEMATA view has the following schema:

Column nameData typeValue
catalog_nameSTRINGThe name of the project that contains the dataset
schema_nameSTRINGThe dataset's name also referred to as thedatasetId
schema_ownerSTRINGThe value is alwaysNULL
creation_timeTIMESTAMPThe dataset's creation time
last_modified_timeTIMESTAMPThe dataset's last modified time
locationSTRINGThe dataset's geographic location
ddlSTRINGTheCREATE SCHEMA DDL statement that can be used to create the dataset
default_collation_nameSTRING The name of the defaultcollation specification if it exists; otherwise,NULL.
sync_statusJSONThe status of the sync between the primary and secondary replicas forcross-region replication anddisaster recovery datasets. ReturnsNULL if the replica is a primary replica or the dataset doesn't use replication.

For stability, we recommend that you explicitly list columns in your information schema queries instead ofusing a wildcard (SELECT *). Explicitly listing columns prevents queries frombreaking if the underlying schema changes.

Scope and syntax

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

View NameResource scopeRegion scope
[PROJECT_ID.]INFORMATION_SCHEMA.SCHEMATAProject levelUS region
[PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.SCHEMATAProject levelREGION
Replace the following:
  • Optional:PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.
  • REGION: anydataset region name. For example,`region-us`.

    Note: You must usea region qualifier to queryINFORMATION_SCHEMA views. The location of the query execution must match the region of theINFORMATION_SCHEMA view.

Example

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

Example

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

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

SELECT*EXCEPT(schema_owner)FROMINFORMATION_SCHEMA.SCHEMATA;
Note:INFORMATION_SCHEMA view names are case-sensitive.

The result is similar to the following. For readability, some columnsare 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"                          ||                |               |                     |                     |            | );                                       |+----------------+---------------+---------------------+---------------------+------------+------------------------------------------+

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.