Mantenha tudo organizado com as coleções
Salve e categorize o conteúdo com base nas suas preferências.
Visualização ROUTINE_OPTIONS
A visualização INFORMATION_SCHEMA.ROUTINE_OPTIONS contém uma linha para cada opção
de cada rotina em um conjunto de dados.
Permissões necessárias
Para consultar a visualização INFORMATION_SCHEMA.ROUTINE_OPTIONS, você precisa das seguintes
permissões de gerenciamento de identidade e acesso (IAM, na sigla em inglês):
bigquery.routines.get
bigquery.routines.list
Cada um dos seguintes papéis predefinidos do IAM inclui as
permissões necessárias para receber metadados de rotina:
Quando você consulta a visualização INFORMATION_SCHEMA.ROUTINE_OPTIONS, os resultados da consulta contêm uma linha para cada opção de cada rotina em um conjunto de dados.
A visualização INFORMATION_SCHEMA.ROUTINE_OPTIONS tem o seguinte esquema:
Nome da coluna
Tipo de dados
Valor
SPECIFIC_CATALOG
STRING
O nome do projeto que contém a rotina em que a opção está definida
SPECIFIC_SCHEMA
STRING
O nome do conjunto de dados que contém a rotina em que a opção está definida
Os nomes das bibliotecas referenciadas na rotina. Aplicável apenas a UDFs JavaScript
data_governance_type
DataGovernanceType
O nome do tipo de governança de dados compatível. Por exemplo,
DATA_MASKING.
Escopo e sintaxe
As consultas nessa visualização precisam incluir um conjunto de dados ou um qualificador de região. Para mais
informações, consulte Sintaxe.
A tabela a seguir explica os escopos de região e recurso dessa visualização:
-- 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;
Exemplo
Exemplo 1:
O exemplo a seguir recupera as opções de rotina para todas
as rotinas em mydataset no seu projeto padrão (myproject) consultando a
visualização INFORMATION_SCHEMA.ROUTINE_OPTIONS:
[[["Fácil de entender","easyToUnderstand","thumb-up"],["Meu problema foi resolvido","solvedMyProblem","thumb-up"],["Outro","otherUp","thumb-up"]],[["Difícil de entender","hardToUnderstand","thumb-down"],["Informações incorretas ou exemplo de código","incorrectInformationOrSampleCode","thumb-down"],["Não contém as informações/amostras de que eu preciso","missingTheInformationSamplesINeed","thumb-down"],["Problema na tradução","translationIssue","thumb-down"],["Outro","otherDown","thumb-down"]],["Última atualização 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```"]]
