Manage routines
In BigQuery, routines are a resource type that includes the
following:
This document describes tasks that are common to all routine types in
BigQuery.
Permissions
To reference a routine in a SQL query, you must have the bigquery.routines.get
permission. To grant access to routines you can grant an IAM role
with the bigquery.routines.get permission on the dataset or on the individual
routine. Granting access at the dataset level gives the principal access to all
routines in the dataset. For more information, see
Control access to resources with IAM.
By default, you also need permission to access any resources that the routine
references, such as tables or views. For UDFs and table functions, you can
authorize the function to access those resources on the caller's behalf. For
more information, see
Authorized functions.
Create a routine
To create a routine, you must have the bigquery.routines.create permission.
SQL
Depending on the routine type, run one of the following DDL statements:
List routines
To list the routines in a dataset, you must have the bigquery.routines.get and
bigquery.routines.list permissions.
Console
In the Trusted Cloud console, open the BigQuery page.
Go to BigQuery
In the Explorer panel, expand your project and select a dataset.
Expand the Routines list.
SQL
Query the INFORMATION_SCHEMA.ROUTINES view:
In the Trusted Cloud console, go to the BigQuery page.
Go to BigQuery
In the query editor, enter the following statement:
SELECT
COLUMN_LIST
FROM
{ DATASET | REGION }.INFORMATION_SCHEMA.ROUTINES;
Click play_circle Run.
For more information about how to run queries, see Run an interactive query.
Replace the following:
Example:
SELECT
routine_name, routine_type, routine_body
FROM
mydataset.INFORMATION_SCHEMA.ROUTINES;
+------------------+----------------+--------------+
| routine_name | routine_type | routine_body |
+------------------+----------------+--------------+
| AddFourAndDivide | FUNCTION | SQL |
| create_customer | PROCEDURE | SQL |
| names_by_year | TABLE FUNCTION | SQL |
+------------------+----------------+--------------+
bq
Use the bq ls command
with the --routines flag:
bq ls --routines DATASET
Replace the following:
- DATASET: the name of a dataset in your project.
Example:
bq ls --routines mydataset
Id Routine Type Language Creation Time Last Modified Time
------------------ ----------------------- ---------- ----------------- --------------------
AddFourAndDivide SCALAR_FUNCTION SQL 05 May 01:12:03 05 May 01:12:03
create_customer PROCEDURE SQL 21 Apr 19:55:51 21 Apr 19:55:51
names_by_year TABLE_VALUED_FUNCTION SQL 01 Sep 22:59:17 01 Sep 22:59:17
View the body of a routine
To view the body of a routine, you must have the bigquery.routines.get permission.
Console
In the Trusted Cloud console, open the BigQuery page.
Go to BigQuery
In the Explorer panel, expand your project and select a dataset.
Expand the Routines list.
Select the routine. The body of the routine is listed under Routine
query.
SQL
Select the routine_definition column of the
INFORMATION_SCHEMA.ROUTINES view:
In the Trusted Cloud console, go to the BigQuery page.
Go to BigQuery
In the query editor, enter the following statement:
SELECT
routine_definition
FROM
{ DATASET | REGION }.INFORMATION_SCHEMA.ROUTINES
WHERE
routine_name = ROUTINE_NAME;
Click play_circle Run.
For more information about how to run queries, see Run an interactive query.
Replace the following:
- DATASET: the name of a dataset in your project.
- REGION: a region qualifier.
- ROUTINE_NAME: the name of the routine.
Example:
SELECT
routine_definition
FROM
mydataset.INFORMATION_SCHEMA.ROUTINES
WHERE
routine_name = 'AddFourAndDivide';
+--------------------+
| routine_definition |
+--------------------+
| (x + 4) / y |
+--------------------+
bq
Use the bq show command
with the --routine flag:
bq show --routine DATASET.ROUTINE_NAME
Replace the following:
- DATASET: the name of a dataset in your project.
- ROUTINE_NAME: the name of the routine.
Example:
bq show --routine mydataset.AddFourAndDivide
Id Routine Type Language Signature Definition Creation Time Last Modified Time
------------------ ----------------- ---------- ------------------------------- ------------- ----------------- --------------------
AddFourAndDivide SCALAR_FUNCTION SQL (x INT64, y INT64) -> FLOAT64 (x + 4) / y 05 May 01:12:03 05 May 01:12:03
Delete a routine
To delete a routine, you must have the bigquery.routines.delete permission.
Console
In the Trusted Cloud console, open the BigQuery page.
Go to BigQuery
In the Explorer panel, expand your project and select a dataset.
Expand the Routines list.
Select the routine.
Expand the
more_vert
Actions option and click Delete.
Type "delete" in the dialog, then click Delete to confirm.
SQL
Depending on the routine type, run one of the following DDL statements:
Example:
DROP FUNCTION IF EXISTS mydataset.AddFourAndDivide
bq
Use the bq rm command
with the --routine flag:
bq rm --routine DATASET.ROUTINE_NAME
Replace the following:
- DATASET: the name of a dataset in your project.
- ROUTINE_NAME: the name of the routine.
Example:
bq rm --routine mydataset.AddFourAndDivide
Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
Last updated 2025-08-25 UTC.
[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Missing the information I need","missingTheInformationINeed","thumb-down"],["Too complicated / too many steps","tooComplicatedTooManySteps","thumb-down"],["Out of date","outOfDate","thumb-down"],["Samples / code issue","samplesCodeIssue","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-08-25 UTC."],[[["\u003cp\u003eBigQuery routines include stored procedures, user-defined functions (UDFs), and table functions, each with specific creation and management methods.\u003c/p\u003e\n"],["\u003cp\u003eExecuting, listing, and viewing the body of a routine requires specific permissions such as \u003ccode\u003ebigquery.routines.get\u003c/code\u003e, \u003ccode\u003ebigquery.routines.list\u003c/code\u003e, and the user needs to have permissions to access any resources referenced by the routine, such as tables or views.\u003c/p\u003e\n"],["\u003cp\u003eRoutines can be created, viewed, listed, and deleted using SQL DDL statements, the \u003ccode\u003ebq\u003c/code\u003e command-line tool, or through the BigQuery API using a method call.\u003c/p\u003e\n"],["\u003cp\u003eTo list routines, you can use the Google Cloud console, query the \u003ccode\u003eINFORMATION_SCHEMA.ROUTINES\u003c/code\u003e view, use the \u003ccode\u003ebq ls\u003c/code\u003e command with the \u003ccode\u003e--routines\u003c/code\u003e flag, or call the \u003ccode\u003eroutines.list\u003c/code\u003e API method.\u003c/p\u003e\n"],["\u003cp\u003eTo view the body of a routine you can use the Google Cloud console, query the \u003ccode\u003eINFORMATION_SCHEMA.ROUTINES\u003c/code\u003e view, use the \u003ccode\u003ebq show\u003c/code\u003e command with the \u003ccode\u003e--routine\u003c/code\u003e flag, or use the \u003ccode\u003eroutines.get\u003c/code\u003e method.\u003c/p\u003e\n"]]],[],null,["# Manage routines\n===============\n\nIn BigQuery, *routines* are a resource type that includes the\nfollowing:\n\n- [Stored procedures](/bigquery/docs/procedures#writing_a_procedure).\n- [User-defined functions](/bigquery/docs/user-defined-functions) (UDFs), including [remote functions](/bigquery/docs/remote-functions).\n- [Table functions](/bigquery/docs/table-functions).\n\nThis document describes tasks that are common to all routine types in\nBigQuery.\n\nPermissions\n-----------\n\nTo reference a routine in a SQL query, you must have the `bigquery.routines.get`\npermission. To grant access to routines you can grant an IAM role\nwith the `bigquery.routines.get` permission on the dataset or on the individual\nroutine. Granting access at the dataset level gives the principal access to all\nroutines in the dataset. For more information, see\n[Control access to resources with IAM](/bigquery/docs/control-access-to-resources-iam).\n\nBy default, you also need permission to access any resources that the routine\nreferences, such as tables or views. For UDFs and table functions, you can\n*authorize* the function to access those resources on the caller's behalf. For\nmore information, see\n[Authorized functions](/bigquery/docs/authorized-functions).\n\nCreate a routine\n----------------\n\nTo create a routine, you must have the `bigquery.routines.create` permission. \n\n### SQL\n\nDepending on the routine type, run one of the following DDL statements:\n\n- [Stored procedure: `CREATE PROCEDURE`](/bigquery/docs/reference/standard-sql/data-definition-language#create_procedure)\n- [User-defined function: `CREATE FUNCTION`](/bigquery/docs/reference/standard-sql/data-definition-language#create_function_statement)\n- [Table function: `CREATE TABLE FUNCTION`](/bigquery/docs/reference/standard-sql/data-definition-language#create_table_function_statement)\n\n### API\n\nCall the [`routines.insert` method](/bigquery/docs/reference/rest/v2/routines/insert)\nwith a defined\n[`Routine` resource](/bigquery/docs/reference/rest/v2/routines#Routine).\n\nList routines\n-------------\n\nTo list the routines in a dataset, you must have the `bigquery.routines.get` and\n`bigquery.routines.list` permissions. \n\n### Console\n\n1. In the Google Cloud console, open the BigQuery page.\n\n [Go to BigQuery](https://console.cloud.google.com/bigquery)\n2. In the **Explorer** panel, expand your project and select a dataset.\n\n3. Expand the **Routines** list.\n\n### SQL\n\nQuery the [`INFORMATION_SCHEMA.ROUTINES` view](/bigquery/docs/information-schema-routines):\n\n\u003cbr /\u003e\n\n1. In the Google Cloud console, go to the **BigQuery** page.\n\n [Go to BigQuery](https://console.cloud.google.com/bigquery)\n2. In the query editor, enter the following statement:\n\n ```googlesql\n SELECT\n COLUMN_LIST\n FROM\n { DATASET | REGION }.INFORMATION_SCHEMA.ROUTINES;\n ```\n\n \u003cbr /\u003e\n\n3. Click play_circle **Run**.\n\n \u003cbr /\u003e\n\nFor more information about how to run queries, see [Run an interactive query](/bigquery/docs/running-queries#queries).\n\nReplace the following:\n\n- \u003cvar translate=\"no\"\u003eCOLUMN_LIST\u003c/var\u003e: a comma-separated list of columns from the [`INFORMATION_SCHEMA.ROUTINES` view](/bigquery/docs/information-schema-routines).\n- \u003cvar translate=\"no\"\u003eDATASET\u003c/var\u003e: the name of a dataset in your project.\n- \u003cvar translate=\"no\"\u003eREGION\u003c/var\u003e: a [region qualifier](/bigquery/docs/information-schema-intro#region_qualifier).\n\nExample: \n\n```googlesql\nSELECT\n routine_name, routine_type, routine_body\nFROM\n mydataset.INFORMATION_SCHEMA.ROUTINES;\n``` \n\n +------------------+----------------+--------------+\n | routine_name | routine_type | routine_body |\n +------------------+----------------+--------------+\n | AddFourAndDivide | FUNCTION | SQL |\n | create_customer | PROCEDURE | SQL |\n | names_by_year | TABLE FUNCTION | SQL |\n +------------------+----------------+--------------+\n\n### bq\n\nUse the [`bq ls` command](/bigquery/docs/reference/bq-cli-reference#bq_ls)\nwith the `--routines` flag: \n\n```bash\nbq ls --routines DATASET\n```\n\nReplace the following:\n\n- \u003cvar translate=\"no\"\u003eDATASET\u003c/var\u003e: the name of a dataset in your project.\n\nExample: \n\n```bash\nbq ls --routines mydataset\n``` \n\n Id Routine Type Language Creation Time Last Modified Time\n ------------------ ----------------------- ---------- ----------------- --------------------\n AddFourAndDivide SCALAR_FUNCTION SQL 05 May 01:12:03 05 May 01:12:03\n create_customer PROCEDURE SQL 21 Apr 19:55:51 21 Apr 19:55:51\n names_by_year TABLE_VALUED_FUNCTION SQL 01 Sep 22:59:17 01 Sep 22:59:17\n\n### API\n\nCall the [`routines.list` method](/bigquery/docs/reference/rest/v2/routines/list)\nwith the dataset ID.\n\nView the body of a routine\n--------------------------\n\nTo view the body of a routine, you must have the `bigquery.routines.get` permission. \n\n### Console\n\n1. In the Google Cloud console, open the BigQuery page.\n\n [Go to BigQuery](https://console.cloud.google.com/bigquery)\n2. In the **Explorer** panel, expand your project and select a dataset.\n\n3. Expand the **Routines** list.\n\n4. Select the routine. The body of the routine is listed under **Routine\n query**.\n\n### SQL\n\nSelect the `routine_definition` column of the\n[`INFORMATION_SCHEMA.ROUTINES` view](/bigquery/docs/information-schema-routines):\n\n\u003cbr /\u003e\n\n1. In the Google Cloud console, go to the **BigQuery** page.\n\n [Go to BigQuery](https://console.cloud.google.com/bigquery)\n2. In the query editor, enter the following statement:\n\n ```googlesql\n SELECT\n routine_definition\n FROM\n { DATASET | REGION }.INFORMATION_SCHEMA.ROUTINES\n WHERE\n routine_name = ROUTINE_NAME;\n ```\n\n \u003cbr /\u003e\n\n3. Click play_circle **Run**.\n\n \u003cbr /\u003e\n\nFor more information about how to run queries, see [Run an interactive query](/bigquery/docs/running-queries#queries).\n\nReplace the following:\n\n- \u003cvar translate=\"no\"\u003eDATASET\u003c/var\u003e: the name of a dataset in your project.\n- \u003cvar translate=\"no\"\u003eREGION\u003c/var\u003e: a [region qualifier](/bigquery/docs/information-schema-intro#region_qualifier).\n- \u003cvar translate=\"no\"\u003eROUTINE_NAME\u003c/var\u003e: the name of the routine.\n\nExample: \n\n```googlesql\nSELECT\n routine_definition\nFROM\n mydataset.INFORMATION_SCHEMA.ROUTINES\nWHERE\n routine_name = 'AddFourAndDivide';\n``` \n\n +--------------------+\n | routine_definition |\n +--------------------+\n | (x + 4) / y |\n +--------------------+\n\n### bq\n\nUse the [`bq show` command](/bigquery/docs/reference/bq-cli-reference#bq_show)\nwith the `--routine` flag: \n\n```bash\nbq show --routine DATASET.ROUTINE_NAME\n```\n\nReplace the following:\n\n- \u003cvar translate=\"no\"\u003eDATASET\u003c/var\u003e: the name of a dataset in your project.\n- \u003cvar translate=\"no\"\u003eROUTINE_NAME\u003c/var\u003e: the name of the routine.\n\nExample: \n\n```bash\nbq show --routine mydataset.AddFourAndDivide\n``` \n\n Id Routine Type Language Signature Definition Creation Time Last Modified Time\n ------------------ ----------------- ---------- ------------------------------- ------------- ----------------- --------------------\n AddFourAndDivide SCALAR_FUNCTION SQL (x INT64, y INT64) -\u003e FLOAT64 (x + 4) / y 05 May 01:12:03 05 May 01:12:03\n\n### API\n\nCall the [`routines.get` method](/bigquery/docs/reference/rest/v2/routines/get)\nwith the dataset ID and the name of the routine. The body of the\nroutine is returned in the\n[`Routine` object](/bigquery/docs/reference/rest/v2/routines#Routine).\n\nDelete a routine\n----------------\n\nTo delete a routine, you must have the `bigquery.routines.delete` permission. \n\n### Console\n\n1. In the Google Cloud console, open the BigQuery page.\n\n [Go to BigQuery](https://console.cloud.google.com/bigquery)\n2. In the **Explorer** panel, expand your project and select a dataset.\n\n3. Expand the **Routines** list.\n\n4. Select the routine.\n\n5. Expand the\n more_vert\n **Actions** option and click **Delete**.\n\n6. Type `\"delete\"` in the dialog, then click **Delete** to confirm.\n\n### SQL\n\nDepending on the routine type, run one of the following DDL statements:\n\n- [Stored procedure: `DROP PROCEDURE`](/bigquery/docs/reference/standard-sql/data-definition-language#drop_procedure_statement)\n- [User-defined function: `DROP FUNCTION`](/bigquery/docs/reference/standard-sql/data-definition-language#drop_function_statement)\n- [Table function: `DROP TABLE FUNCTION`](/bigquery/docs/reference/standard-sql/data-definition-language#drop_table_function)\n\nExample: \n\n DROP FUNCTION IF EXISTS mydataset.AddFourAndDivide\n\n### bq\n\nUse the [`bq rm` command](/bigquery/docs/reference/bq-cli-reference#bq_rm)\nwith the `--routine` flag: \n\n```bash\nbq rm --routine DATASET.ROUTINE_NAME\n```\n\nReplace the following:\n\n- \u003cvar translate=\"no\"\u003eDATASET\u003c/var\u003e: the name of a dataset in your project.\n- \u003cvar translate=\"no\"\u003eROUTINE_NAME\u003c/var\u003e: the name of the routine.\n\nExample: \n\n bq rm --routine mydataset.AddFourAndDivide\n\n### API\n\nCall the [`routines.delete` method](/bigquery/docs/reference/rest/v2/routines/delete)\nwith the dataset ID and the name of the routine."]]
