Der Name der Verbindung, falls vorhanden. Andernfalls
NULL
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.ROUTINES;-- Returns metadata for routines in a region.SELECT*FROMregion-us.INFORMATION_SCHEMA.ROUTINES;
Beispiel
Beispiel
Wenn Sie die Abfrage für ein anderes Projekt als Ihr Standardprojekt ausführen möchten, fügen Sie dem Dataset die Projekt-ID im folgenden Format hinzu:
Im folgenden Beispiel werden alle Spalten der Ansicht INFORMATION_SCHEMA.ROUTINES abgerufen. Die zurückgegebenen Metadaten gelten für alle Routinen in mydataset in Ihrem Standardprojekt myproject. Das Dataset mydataset enthält eine Routine namens myroutine1.
[[["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.ROUTINES\u003c/code\u003e view provides metadata for each routine within a dataset, with each row representing a single routine.\u003c/p\u003e\n"],["\u003cp\u003eQuerying this view requires specific IAM permissions, namely \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 the \u003ccode\u003eINFORMATION_SCHEMA.ROUTINES\u003c/code\u003e view includes details such as \u003ccode\u003eSPECIFIC_CATALOG\u003c/code\u003e, \u003ccode\u003eSPECIFIC_SCHEMA\u003c/code\u003e, \u003ccode\u003eSPECIFIC_NAME\u003c/code\u003e, \u003ccode\u003eROUTINE_TYPE\u003c/code\u003e, \u003ccode\u003eDATA_TYPE\u003c/code\u003e, \u003ccode\u003eROUTINE_BODY\u003c/code\u003e, \u003ccode\u003eROUTINE_DEFINITION\u003c/code\u003e, \u003ccode\u003eCREATED\u003c/code\u003e, and \u003ccode\u003eLAST_ALTERED\u003c/code\u003e.\u003c/p\u003e\n"],["\u003cp\u003eQueries against the \u003ccode\u003eINFORMATION_SCHEMA.ROUTINES\u003c/code\u003e view must specify a dataset or a region qualifier, ensuring the query execution location matches the region of the \u003ccode\u003eINFORMATION_SCHEMA\u003c/code\u003e view.\u003c/p\u003e\n"],["\u003cp\u003e\u003ccode\u003eINFORMATION_SCHEMA\u003c/code\u003e view names are case-sensitive and routines are defined as \u003ccode\u003eFUNCTION\u003c/code\u003e, \u003ccode\u003ePROCEDURE\u003c/code\u003e, or \u003ccode\u003eTABLE FUNCTION\u003c/code\u003e which impacts the data returned from the query.\u003c/p\u003e\n"]]],[],null,["# ROUTINES view\n=============\n\nThe `INFORMATION_SCHEMA.ROUTINES` view contains one row for each routine in a\ndataset.\n\nRequired permissions\n--------------------\n\nTo query the `INFORMATION_SCHEMA.ROUTINES` 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.ROUTINES` view, the query results contain\none row for each routine in a dataset.\n\nThe `INFORMATION_SCHEMA.ROUTINES` view has the following schema:\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.ROUTINES;\n\n -- Returns metadata for routines in a region.\n SELECT * FROM region-us.INFORMATION_SCHEMA.ROUTINES;\n\nExample\n-------\n\n#### Example\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.ROUTINES\n```\n. For example, ```myproject`.INFORMATION_SCHEMA.ROUTINES``.\n\n\u003cbr /\u003e\n\nThe following example retrieves all columns from the\n`INFORMATION_SCHEMA.ROUTINES` view. The metadata returned is for all routines in\n`mydataset` in your default project --- `myproject`. The dataset `mydataset`\ncontains a routine named `myroutine1`. \n\n```googlesql\nSELECT\n *\nFROM\n mydataset.INFORMATION_SCHEMA.ROUTINES;\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 | routine_catalog | routine_schema | routine_name | routine_type | data_type | routine_body | routine_definition | external_language | is_deterministic | security_type | created | last_altered | ddl |\n+------------------+-----------------+---------------+-----------------+----------------+--------------+--------------+-----------+--------------+--------------------+-------------------+------------------+---------------+-----------------------------+-----------------------------+-----------------------------------------------------------+\n| myproject | mydataset | myroutine1 | myproject | mydataset | myroutine1 | FUNCTION | NULL | SQL | x + 3 | NULL | NULL | NULL | 2019-10-03 17:29:00.235 UTC | 2019-10-03 17:29:00.235 UTC | CREATE FUNCTION myproject.mydataset.myroutine1(x FLOAT64) |\n| | | | | | | | | | | | | | | | AS ( |\n| | | | | | | | | | | | | | | | x + 3 |\n| | | | | | | | | | | | | | | | ); |\n+------------------+-----------------+---------------+-----------------+----------------+--------------+--------------+-----------+--------------+--------------------+-------------------+------------------+---------------+-----------------------------+-----------------------------+-----------------------------------------------------------+\n```"]]
