Wenn Sie die Ansicht INFORMATION_SCHEMA.ROUTINE_OPTIONS abfragen, wird im Ergebnis jede Option jeder Routine in einem Dataset in einer eigenen Zeile dargestellt.
Die Ansicht INFORMATION_SCHEMA.ROUTINE_OPTIONS hat das folgende Schema:
Spaltenname
Datentyp
Wert
SPECIFIC_CATALOG
STRING
Der Name des Projekts, das die Routine enthält, in dem die Option definiert ist
SPECIFIC_SCHEMA
STRING
Der Name des Datasets, das die Routine enthält, in dem die Option definiert ist
Die Namen der Bibliotheken, auf die in der Routine verwiesen wird. Gilt nur für JavaScript-UDFs
data_governance_type
DataGovernanceType
Der Name des unterstützten Data-Governance-Typs. Beispiel: DATA_MASKING.
Bereich und Syntax
Für Abfragen dieser Ansicht muss ein Dataset- oder Regions-Qualifier verwendet werden. Weitere Informationen finden Sie unter Syntax.
In der folgenden Tabelle werden die Regions- und Ressourcenbereiche für diese Ansicht erläutert:
DATASET_ID: die ID Ihres Datasets. Weitere Informationen finden Sie unter Dataset-Qualifier.
Beispiel
-- Returns metadata for routines in a single dataset.SELECT*FROMmyDataset.INFORMATION_SCHEMA.ROUTINE_OPTIONS;-- Returns metadata for routines in a region.SELECT*FROMregion-us.INFORMATION_SCHEMA.ROUTINE_OPTIONS;
Beispiel
Beispiel 1:
Im folgenden Beispiel werden die Routineoptionen für alle Routinen in mydataset in Ihrem Standardprojekt myproject durch Abfrage der Ansicht INFORMATION_SCHEMA.ROUTINE_OPTIONS abgerufen.
[[["Leicht verständlich","easyToUnderstand","thumb-up"],["Mein Problem wurde gelöst","solvedMyProblem","thumb-up"],["Sonstiges","otherUp","thumb-up"]],[["Schwer verständlich","hardToUnderstand","thumb-down"],["Informationen oder Beispielcode falsch","incorrectInformationOrSampleCode","thumb-down"],["Benötigte Informationen/Beispiele nicht gefunden","missingTheInformationSamplesINeed","thumb-down"],["Problem mit der Übersetzung","translationIssue","thumb-down"],["Sonstiges","otherDown","thumb-down"]],["Zuletzt aktualisiert: 2025-08-17 (UTC)."],[[["\u003cp\u003eThe \u003ccode\u003eINFORMATION_SCHEMA.ROUTINE_OPTIONS\u003c/code\u003e view provides detailed information about the options configured for each routine within a dataset, with each row representing one option of a routine.\u003c/p\u003e\n"],["\u003cp\u003eAccessing this view requires specific IAM permissions, including \u003ccode\u003ebigquery.routines.get\u003c/code\u003e and \u003ccode\u003ebigquery.routines.list\u003c/code\u003e, which are included in predefined roles like \u003ccode\u003eroles/bigquery.admin\u003c/code\u003e, \u003ccode\u003eroles/bigquery.metadataViewer\u003c/code\u003e, and \u003ccode\u003eroles/bigquery.dataViewer\u003c/code\u003e.\u003c/p\u003e\n"],["\u003cp\u003eThe schema of \u003ccode\u003eINFORMATION_SCHEMA.ROUTINE_OPTIONS\u003c/code\u003e includes columns such as \u003ccode\u003eSPECIFIC_CATALOG\u003c/code\u003e, \u003ccode\u003eSPECIFIC_SCHEMA\u003c/code\u003e, \u003ccode\u003eSPECIFIC_NAME\u003c/code\u003e, \u003ccode\u003eOPTION_NAME\u003c/code\u003e, \u003ccode\u003eOPTION_TYPE\u003c/code\u003e, and \u003ccode\u003eOPTION_VALUE\u003c/code\u003e, detailing the project, dataset, routine, and option properties.\u003c/p\u003e\n"],["\u003cp\u003eQueries against the \u003ccode\u003eROUTINE_OPTIONS\u003c/code\u003e view must specify either a dataset or a region qualifier to define the scope, and the location of query execution must match the region of the \u003ccode\u003eINFORMATION_SCHEMA\u003c/code\u003e view.\u003c/p\u003e\n"],["\u003cp\u003eThe available options for each routine are detailed in the Options table, containing columns for \u003ccode\u003eOPTION_NAME\u003c/code\u003e, \u003ccode\u003eOPTION_TYPE\u003c/code\u003e, and \u003ccode\u003eOPTION_VALUE\u003c/code\u003e and containing properties such as description, library and data governance type.\u003c/p\u003e\n"]]],[],null,["# ROUTINE_OPTIONS view\n====================\n\nThe `INFORMATION_SCHEMA.ROUTINE_OPTIONS` view contains one row for each option\nof each routine in a dataset.\n\nRequired permissions\n--------------------\n\nTo query the `INFORMATION_SCHEMA.ROUTINE_OPTIONS` view, you need the following\nIdentity and Access Management (IAM) permissions:\n\n- `bigquery.routines.get`\n- `bigquery.routines.list`\n\nEach of the following predefined IAM roles includes the\npermissions that you need in order to get routine metadata:\n\n- `roles/bigquery.admin`\n- `roles/bigquery.metadataViewer`\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.ROUTINE_OPTIONS` view, the query results\ncontain one row for each option of each routine in a dataset.\n\nThe `INFORMATION_SCHEMA.ROUTINE_OPTIONS` view has the following schema:\n\n##### Options table\n\nScope and syntax\n----------------\n\nQueries against this view must include a dataset or a region qualifier. For more\ninformation see [Syntax](/bigquery/docs/information-schema-intro#syntax).\nThe following table explains the region and resource scopes 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- \u003cvar translate=\"no\"\u003eDATASET_ID\u003c/var\u003e: the ID of your dataset. For more information, see [Dataset qualifier](/bigquery/docs/information-schema-intro#dataset_qualifier).\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 routines in a single dataset.\n SELECT * FROM myDataset.INFORMATION_SCHEMA.ROUTINE_OPTIONS;\n\n -- Returns metadata for routines in a region.\n SELECT * FROM region-us.INFORMATION_SCHEMA.ROUTINE_OPTIONS;\n\nExample\n-------\n\n##### Example 1:\n\nThe following example retrieves the routine options for all\nroutines in `mydataset` in your default project (`myproject`) by querying the\n`INFORMATION_SCHEMA.ROUTINE_OPTIONS` view: \n\n```googlesql\nSELECT\n *\nFROM\n mydataset.INFORMATION_SCHEMA.ROUTINE_OPTIONS;\n```\n| **Note:** `INFORMATION_SCHEMA` view names are case-sensitive.\n\nThe result is similar to the following: \n\n```\n+-------------------+------------------+---------------+----------------------+---------------+------------------+\n| specific_catalog | specific_schema | specific_name | option_name | option_type | option_value |\n+-------------------+------------------+---------------+----------------------+---------------+------------------+\n| myproject | mydataset | myroutine1 | description | STRING | \"a description\" |\n| myproject | mydataset | myroutine2 | library | ARRAY\u003cSTRING\u003e | [\"a.js\", \"b.js\"] |\n+-------------------+------------------+---------------+----------------------+---------------+------------------+\n```"]]
