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 thebigquery.routines.getpermission. To grant access to routines you can grant an IAM role
with thebigquery.routines.getpermission 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, seeControl 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 canauthorizethe function to access those resources on the caller's behalf. For
more information, seeAuthorized functions.
Create a routine
To create a routine, you must have thebigquery.routines.createpermission.
SQL
Depending on the routine type, run one of the following DDL statements:
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
API
Call theroutines.getmethodwith the dataset ID and the name of the routine. The body of the
routine is returned in theRoutineobject.
Delete a routine
To delete a routine, you must have thebigquery.routines.deletepermission.
Console
In the Google Cloud console, open the BigQuery page.
[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-09-04 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."]]
