Stay organized with collections
Save and categorize content based on your preferences.
SCHEMATA_OPTIONS view
The INFORMATION_SCHEMA.SCHEMATA_OPTIONS view contains one row for each option
that is set in each dataset in a project.
Before you begin
To query the SCHEMATA_OPTIONS
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_OPTIONS view:
When you query the INFORMATION_SCHEMA.SCHEMATA_OPTIONS view, the query results
contain one row for each option that is set in each dataset in a project.
The INFORMATION_SCHEMA.SCHEMATA_OPTIONS 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 name of the dataset, also referred to as the datasetId
OPTION_NAME
STRING
The name of the option. For a list of supported options, see the
schema options list.
The storage_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 is
LOGICAL.
OPTION_TYPE
STRING
The data type of the option
OPTION_VALUE
STRING
The value of the option
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:
[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-08-29 UTC."],[[["\u003cp\u003eThe \u003ccode\u003eINFORMATION_SCHEMA.SCHEMATA_OPTIONS\u003c/code\u003e view provides detailed information about each option set within datasets in a project, with one row per option.\u003c/p\u003e\n"],["\u003cp\u003eTo access this view, users require the \u003ccode\u003ebigquery.datasets.get\u003c/code\u003e IAM permission, which is included in predefined roles like \u003ccode\u003eroles/bigquery.admin\u003c/code\u003e, \u003ccode\u003eroles/bigquery.dataEditor\u003c/code\u003e, \u003ccode\u003eroles/bigquery.dataOwner\u003c/code\u003e, and \u003ccode\u003eroles/bigquery.dataViewer\u003c/code\u003e.\u003c/p\u003e\n"],["\u003cp\u003eThe view's schema includes columns such as \u003ccode\u003eCATALOG_NAME\u003c/code\u003e, \u003ccode\u003eSCHEMA_NAME\u003c/code\u003e, \u003ccode\u003eOPTION_NAME\u003c/code\u003e, \u003ccode\u003eOPTION_TYPE\u003c/code\u003e, and \u003ccode\u003eOPTION_VALUE\u003c/code\u003e, detailing project, dataset, option names, types, and their values, respectively.\u003c/p\u003e\n"],["\u003cp\u003eQueries against \u003ccode\u003eSCHEMATA_OPTIONS\u003c/code\u003e must include a region qualifier (e.g., \u003ccode\u003eregion-us\u003c/code\u003e), and the location of the query execution must align with the \u003ccode\u003eINFORMATION_SCHEMA\u003c/code\u003e view's region.\u003c/p\u003e\n"],["\u003cp\u003eThe view can be used to retrieve specific options like \u003ccode\u003edefault_table_expiration_days\u003c/code\u003e or \u003ccode\u003elabels\u003c/code\u003e for all datasets within a project, and when used, the \u003ccode\u003eINFORMATION_SCHEMA\u003c/code\u003e view names are case-sensitive.\u003c/p\u003e\n"]]],[],null,["# SCHEMATA_OPTIONS view\n=====================\n\nThe `INFORMATION_SCHEMA.SCHEMATA_OPTIONS` view contains one row for each option\nthat is set in each dataset in a project.\n\nBefore you begin\n----------------\n\nTo query the `SCHEMATA_OPTIONS`\nview for dataset metadata, you need the `bigquery.datasets.get`\nIdentity and Access Management (IAM) permission at the project level.\n\nEach of the following predefined IAM roles includes the\npermissions that you need in order to get the `SCHEMATA_OPTIONS` view:\n\n- `roles/bigquery.admin`\n- `roles/bigquery.dataEditor`\n- `roles/bigquery.dataOwner`\n- `roles/bigquery.dataViewer`\n\nFor more information about BigQuery permissions, see\n[Access control with IAM](/bigquery/docs/access-control).\n\nSchema\n------\n\nWhen you query the `INFORMATION_SCHEMA.SCHEMATA_OPTIONS` view, the query results contain one row for each option that is set in each dataset in a project.\n\n\u003cbr /\u003e\n\nThe `INFORMATION_SCHEMA.SCHEMATA_OPTIONS` view has the following schema:\n\n\u003cbr /\u003e\n\nScope and syntax\n----------------\n\nQueries against this view must include a [region\nqualifier](/bigquery/docs/information-schema-intro#syntax). If you do not\nspecify a regional qualifier, metadata is retrieved from the US region.\nThe following table explains the region scope for this view:\n\nReplace the following:\n\n- Optional: \u003cvar translate=\"no\"\u003ePROJECT_ID\u003c/var\u003e: the ID of your Google Cloud project. If not specified, the default project is used.\n- \u003cvar translate=\"no\"\u003eREGION\u003c/var\u003e: any [dataset region name](/bigquery/docs/locations). For example, ```region-us```.\n\n \u003cbr /\u003e\n\n \u003cbr /\u003e\n\n | **Note:** You must use [a region qualifier](/bigquery/docs/information-schema-intro#region_qualifier) to query `INFORMATION_SCHEMA` views. The location of the query execution must match the region of the `INFORMATION_SCHEMA` view.\n\n\u003cbr /\u003e\n\n**Example** \n\n -- Returns metadata for datasets in a region.\n SELECT * FROM region-us.INFORMATION_SCHEMA.SCHEMATA_OPTIONS;\n\nExamples\n--------\n\n#### Retrieve the default table expiration time for all datasets in your project\n\nTo run the query against a project other than your default project, add the\nproject ID to the dataset in the following format: \n\n```bash\n`PROJECT_ID`.INFORMATION_SCHEMA.SCHEMATA_OPTIONS\n```\nfor example, ```myproject`.INFORMATION_SCHEMA.SCHEMATA_OPTIONS``.\n\n\u003cbr /\u003e\n\n```googlesql\nSELECT\n *\nFROM\n INFORMATION_SCHEMA.SCHEMATA_OPTIONS\nWHERE\n option_name = 'default_table_expiration_days';\n```\n| **Note:** `INFORMATION_SCHEMA` view names are case-sensitive.\n\nThe result is similar to the following:\n\n\u003cbr /\u003e\n\n```\n +----------------+---------------+-------------------------------+-------------+---------------------+\n | catalog_name | schema_name | option_name | option_type | option_value |\n +----------------+---------------+-------------------------------+-------------+---------------------+\n | myproject | mydataset3 | default_table_expiration_days | FLOAT64 | 0.08333333333333333 |\n | myproject | mydataset2 | default_table_expiration_days | FLOAT64 | 90.0 |\n | myproject | mydataset1 | default_table_expiration_days | FLOAT64 | 30.0 |\n +----------------+---------------+-------------------------------+-------------+---------------------+\n \n```\n\n\u003cbr /\u003e\n\n| **Note:** `0.08333333333333333` is the floating point representation of 2 hours.\n\n#### Retrieve labels for all datasets in your project\n\nTo run the query against a project other than your default project, add the\nproject ID to the dataset in the following format: \n\n```bash\n`PROJECT_ID`.INFORMATION_SCHEMA.SCHEMATA_OPTIONS\n```\n; for example, ```myproject`.INFORMATION_SCHEMA.SCHEMATA_OPTIONS``.\n\n\u003cbr /\u003e\n\n```googlesql\nSELECT\n *\nFROM\n INFORMATION_SCHEMA.SCHEMATA_OPTIONS\nWHERE\n option_name = 'labels';\n```\n| **Note:** `INFORMATION_SCHEMA` view names are case-sensitive.\n\nThe result is similar to the following:\n\n\u003cbr /\u003e\n\n```\n +----------------+---------------+-------------+---------------------------------+------------------------+\n | catalog_name | schema_name | option_name | option_type | option_value |\n +----------------+---------------+-------------+---------------------------------+------------------------+\n | myproject | mydataset1 | labels | ARRAY\u003cSTRUCT\u003cSTRING, STRING\u003e\u003e | [STRUCT(\"org\", \"dev\")] |\n | myproject | mydataset2 | labels | ARRAY\u003cSTRUCT\u003cSTRING, STRING\u003e\u003e | [STRUCT(\"org\", \"dev\")] |\n +----------------+---------------+-------------+---------------------------------+------------------------+\n \n```\n\n\u003cbr /\u003e\n\n| **Note:** Datasets without labels are excluded from the query results."]]
