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.

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@% |
    +-----------------------------+------------------+---------+

Last modified:

Was this article helpful?