Nama koneksi, jika rutinitas memilikinya. Atau
NULL
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.ROUTINES;-- Returns metadata for routines in a region.SELECT*FROMregion-us.INFORMATION_SCHEMA.ROUTINES;
Contoh
Contoh
Untuk menjalankan kueri terhadap project selain project default Anda, tambahkan project ID ke set data dalam format berikut:
Contoh berikut mengambil semua kolom dari
tampilan INFORMATION_SCHEMA.ROUTINES. Metadata yang ditampilkan adalah untuk semua rutinitas di
mydataset dalam project default Anda — myproject. Set data mydataset
berisi rutinitas bernama myroutine1.
[[["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-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```"]]
