[[["わかりやすい","easyToUnderstand","thumb-up"],["問題の解決に役立った","solvedMyProblem","thumb-up"],["その他","otherUp","thumb-up"]],[["わかりにくい","hardToUnderstand","thumb-down"],["情報またはサンプルコードが不正確","incorrectInformationOrSampleCode","thumb-down"],["必要な情報 / サンプルがない","missingTheInformationSamplesINeed","thumb-down"],["翻訳に関する問題","translationIssue","thumb-down"],["その他","otherDown","thumb-down"]],["最終更新日 2025-09-04 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."]]