SCHEMATA_OPTIONS view

TheINFORMATION_SCHEMA.SCHEMATA_OPTIONS view contains one row for each optionthat is set in each dataset in a project.

Before you begin

To query theSCHEMATA_OPTIONS 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_OPTIONS 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_OPTIONS view, the query resultscontain one row for each option that is set in each dataset in a project.

TheINFORMATION_SCHEMA.SCHEMATA_OPTIONS view has the following schema:

Column nameData typeValue
catalog_nameSTRINGThe name of the project that contains the dataset
schema_nameSTRINGThe name of the dataset, also referred to as thedatasetId
option_nameSTRINGThe name of the option. For a list of supported options, see theschema options list.

Thestorage_billing_model option is only displayed for datasets that have been updated after December 1, 2022. For datasets that were last updated before that date, the storage billing model isLOGICAL.

option_typeSTRINGThe data type of the option
option_valueSTRINGThe value of the option

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.SCHEMATA_OPTIONSProject levelUS region
[PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.SCHEMATA_OPTIONSProject 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_OPTIONS;

Examples

Retrieve the default table expiration time for all datasets in your project

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_OPTIONS
for example,`myproject`.INFORMATION_SCHEMA.SCHEMATA_OPTIONS.

SELECT*FROMINFORMATION_SCHEMA.SCHEMATA_OPTIONSWHEREoption_name='default_table_expiration_days';
Note:INFORMATION_SCHEMA view names are case-sensitive.

The result is similar to the following:

  +----------------+---------------+-------------------------------+-------------+---------------------+  |  catalog_name  |  schema_name  |          option_name          | option_type |    option_value     |  +----------------+---------------+-------------------------------+-------------+---------------------+  | myproject      | mydataset3    | default_table_expiration_days | FLOAT64     | 0.08333333333333333 |  | myproject      | mydataset2    | default_table_expiration_days | FLOAT64     | 90.0                |  | myproject      | mydataset1    | default_table_expiration_days | FLOAT64     | 30.0                |  +----------------+---------------+-------------------------------+-------------+---------------------+

Note:0.08333333333333333 is the floating point representation of 2 hours.

Retrieve labels for all datasets in your project

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_OPTIONS
;for example,`myproject`.INFORMATION_SCHEMA.SCHEMATA_OPTIONS.

SELECT*FROMINFORMATION_SCHEMA.SCHEMATA_OPTIONSWHEREoption_name='labels';
Note:INFORMATION_SCHEMA view names are case-sensitive.

The result is similar to the following:

  +----------------+---------------+-------------+---------------------------------+------------------------+  |  catalog_name  |  schema_name  | option_name |          option_type            |      option_value      |  +----------------+---------------+-------------+---------------------------------+------------------------+  | myproject      | mydataset1    | labels      | ARRAY<STRUCT<STRING, STRING>>   | [STRUCT("org", "dev")] |  | myproject      | mydataset2    | labels      | ARRAY<STRUCT<STRING, STRING>>   | [STRUCT("org", "dev")] |  +----------------+---------------+-------------+---------------------------------+------------------------+

Note: Datasets without labels are excluded from the query results.

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.