Manage routines

In BigQuery,routines are a resource type that includes thefollowing:

This document describes tasks that are common to all routine types inBigQuery.

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 rolewith thebigquery.routines.get permission on the dataset or on the individualroutine. Granting access at the dataset level gives the principal access to allroutines in the dataset. For more information, seeControl access to resources with IAM.

By default, you also need permission to access any resources that the routinereferences, such as tables or views. For UDFs and table functions, you canauthorize the function to access those resources on the caller's behalf. Formore information, seeAuthorized functions.

Create a routine

To create a routine, you must have thebigquery.routines.create permission.

SQL

Depending on the routine type, run one of the following DDL statements:

API

Call theroutines.insert methodwith a definedRoutine resource.

List routines

To list the routines in a dataset, you must have thebigquery.routines.get andbigquery.routines.list permissions.

Console

  1. In the Google Cloud console, open the BigQuery page.

    Go to BigQuery

  2. In the left pane, clickExplorer:

    Highlighted button for the Explorer pane.

    If you don't see the left pane, clickExpand left pane to open the pane.

  3. In theExplorer pane, expand your project, clickDatasets, andthen select a dataset.

  4. Click theRoutines tab.

SQL

Query theINFORMATION_SCHEMA.ROUTINES view:

  1. In the Google Cloud console, go to theBigQuery page.

    Go to BigQuery

  2. In the query editor, enter the following statement:

    SELECTCOLUMN_LISTFROM{DATASET|REGION}.INFORMATION_SCHEMA.ROUTINES;

  3. ClickRun.

For more information about how to run queries, seeRun an interactive query.

Replace the following:

Example:

SELECTroutine_name,routine_type,routine_bodyFROMmydataset.INFORMATION_SCHEMA.ROUTINES;
+------------------+----------------+--------------+|   routine_name   |  routine_type  | routine_body |+------------------+----------------+--------------+| AddFourAndDivide | FUNCTION       | SQL          || create_customer  | PROCEDURE      | SQL          || names_by_year    | TABLE FUNCTION | SQL          |+------------------+----------------+--------------+

bq

Use thebq ls commandwith the--routines flag:

bqls--routinesDATASET

Replace the following:

  • DATASET: the name of a dataset in your project.

Example:

bqls--routinesmydataset
         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

API

Call theroutines.list methodwith the dataset ID.

View the body of a routine

To view the body of a routine, you must have thebigquery.routines.get permission.

Console

  1. In the Google Cloud console, open the BigQuery page.

    Go to BigQuery

  2. In the left pane, clickExplorer:

    Highlighted button for the Explorer pane.

  3. In theExplorer pane, expand your project, clickDatasets, andthen select a dataset.

  4. Click theRoutines tab.

  5. Select the routine. The body of the routine is listed underRoutinequery.

SQL

Select theroutine_definition column of theINFORMATION_SCHEMA.ROUTINES view:

  1. In the Google Cloud console, go to theBigQuery page.

    Go to BigQuery

  2. In the query editor, enter the following statement:

    SELECTroutine_definitionFROM{DATASET|REGION}.INFORMATION_SCHEMA.ROUTINESWHEREroutine_name=ROUTINE_NAME;

  3. ClickRun.

For more information about how to run queries, seeRun an interactive query.

Replace the following:

  • DATASET: the name of a dataset in your project.
  • REGION: aregion qualifier.
  • ROUTINE_NAME: the name of the routine.

Example:

SELECTroutine_definitionFROMmydataset.INFORMATION_SCHEMA.ROUTINESWHEREroutine_name='AddFourAndDivide';
+--------------------+| routine_definition |+--------------------+| (x + 4) / y        |+--------------------+

bq

Use thebq show commandwith the--routine flag:

bqshow--routineDATASET.ROUTINE_NAME

Replace the following:

  • DATASET: the name of a dataset in your project.
  • ROUTINE_NAME: the name of the routine.

Example:

bqshow--routinemydataset.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

API

Call theroutines.get methodwith the dataset ID and the name of the routine. The body of theroutine is returned in theRoutine object.

Delete a routine

To delete a routine, you must have thebigquery.routines.delete permission.

Console

  1. In the Google Cloud console, open the BigQuery page.

    Go to BigQuery

  2. In the left pane, clickExplorer:

    Highlighted button for the Explorer pane.

  3. In theExplorer pane, expand your project, clickDatasets, andthen select a dataset.

  4. Click theRoutines tab.

  5. Select the routine.

  6. In the details pane, clickDelete.

  7. Type"delete" in the dialog, then clickDelete to confirm.

SQL

Depending on the routine type, run one of the following DDL statements:

Example:

DROPFUNCTIONIFEXISTSmydataset.AddFourAndDivide

bq

Use thebq rm commandwith the--routine flag:

bqrm--routineDATASET.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

API

Call theroutines.delete methodwith the dataset ID and the name of the routine.

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.