Important

Helios features are now enabled during weekly update windows and are no longer directly tied to SingleStore engine releases. Refer to the release notes to view the latest features available in your Helios cluster.

SHOW PROCEDURES

The SHOW PROCEDURES command lists existing stored procedures in the current or the specified database.

Refer to Permissions Matrix for the required permission.

Syntax

SHOW PROCEDURES [{FROM | IN} database_name] [LIKE pattern]

Remarks

  • The Routine Lifetime column in the output specifies if the stored procedure is temporary or permanent (non-temporary). Refer to CREATE TEMPORARY PROCEDURE for information on temporary stored procedures.

  • The Definer column in the output specifies the user that created the stored procedure in the 'username'@'hostname' format.

  • If function overloading is enabled, the output of SHOW PROCEDURES includes the procedure's arguments and variant ID. For overloaded procedures, the columns contain the arguments and variant IDs. For non-overloaded procedures, the columns display NULL. Refer to Overloaded Functions and Stored Procedures for more information.

Example - List Stored Procedures

The following examples show how to list stored procedures:

  • List all the stored procedures in the current database:

    SHOW PROCEDURES;
    +----------------------+------------------+---------+
    | Procedures_in_dbTest | Routine Lifetime | Definer |
    +----------------------+------------------+---------+
    | charge_account       | Permanent        | userA@% |
    | courses_sp           | Permanent        | userA@% |
    | temp_sp1             | Temporary        | userA@% |
    +----------------------+------------------+---------+
  • List stored procedures in the specified database:

    SHOW PROCEDURES IN dbExample;
    +-------------------------+------------------+---------+
    | Procedures_in_dbExample | Routine Lifetime | Definer |
    +-------------------------+------------------+---------+
    | sp1                     | Permanent        | userA@% |
    | sp2                     | Permanent        | userA@% |
    | test_sp                 | Permanent        | userA@% |
    | ticket_sales_by_minute  | Permanent        | userA@% |
    | test_sp                 | Temporary        | userA@% |
    +-------------------------+------------------+---------+
  • List the stored procedures that match a specified pattern in the current database:

    SHOW PROCEDURES LIKE '%sp%';
    +-----------------------------+------------------+---------+
    | Procedures_in_dbTest (%sp%) | Routine Lifetime | Definer |
    +-----------------------------+------------------+---------+
    | courses_sp                  | Permanent        | userA@% |
    | temp_sp1                    | Temporary        | userA@% |
    +-----------------------------+------------------+---------+

Example - List Overloaded Stored Procedures

The following example shows the output of SHOW PROCEDURES with overloaded procedures. The procedures from Example 4 - Overloaded Functions and Stored Procedures are used.

SHOW PROCEDURES;
+--------------------+-----------+------------------+------------+---------+
| Procedures_in_test | Arguments | Routine Lifetime | Variant ID | Definer |
+--------------------+-----------+------------------+------------+---------+
| output_type        | BIGINT    | Permanent        |          1 | root@%  |
| output_type        | INT       | Permanent        |          0 | root@%  |
+--------------------+-----------+------------------+------------+---------+

Last modified:

Was this article helpful?