Saat Anda membuat kueri tampilan INFORMATION_SCHEMA.ROUTINE_OPTIONS, hasil kueri
akan berisi satu baris untuk setiap opsi dari setiap rutinitas dalam set data.
Tampilan INFORMATION_SCHEMA.ROUTINE_OPTIONS memiliki skema berikut:
Nama kolom
Jenis data
Nilai
SPECIFIC_CATALOG
STRING
Nama project yang berisi rutinitas tempat
opsi ditentukan
SPECIFIC_SCHEMA
STRING
Nama set data yang berisi rutinitas tempat opsi
ditentukan
Nama library yang dirujuk dalam rutinitas. Hanya berlaku
untuk UDF JavaScript
data_governance_type
DataGovernanceType
Nama jenis tata kelola data yang didukung. Misalnya,
DATA_MASKING
Cakupan dan sintaksis
Kueri terhadap tampilan ini harus menyertakan set data atau penentu region. Untuk informasi
selengkapnya, lihat Sintaksis.
Tabel berikut menjelaskan cakupan region dan resource untuk tabel virtual ini:
DATASET_ID: ID set data Anda. Untuk mengetahui informasi selengkapnya, lihat Penentu set data.
Contoh
-- 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;
Contoh
Contoh 1:
Contoh berikut mengambil opsi rutinitas untuk semua
rutin dalam mydataset dalam project default Anda (myproject) dengan meng-kueri
tampilan INFORMATION_SCHEMA.ROUTINE_OPTIONS:
[[["Mudah dipahami","easyToUnderstand","thumb-up"],["Memecahkan masalah saya","solvedMyProblem","thumb-up"],["Lainnya","otherUp","thumb-up"]],[["Sulit dipahami","hardToUnderstand","thumb-down"],["Informasi atau kode contoh salah","incorrectInformationOrSampleCode","thumb-down"],["Informasi/contoh yang saya butuhkan tidak ada","missingTheInformationSamplesINeed","thumb-down"],["Masalah terjemahan","translationIssue","thumb-down"],["Lainnya","otherDown","thumb-down"]],["Terakhir diperbarui pada 2025-09-04 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```"]]
