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

The following examples show how to list stored procedures:

• List all the stored procedures in the current database:

  SQL

  Copy

  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:

  SQL

  Copy

  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:

  SQL

  Copy

  SHOW PROCEDURES LIKE '%sp%';

  +-----------------------------+------------------+---------+
  | Procedures_in_dbTest (%sp%) | Routine Lifetime | Definer |
  +-----------------------------+------------------+---------+
  | courses_sp                  | Permanent        | userA@% |
  | temp_sp1                    | Temporary        | userA@% |
  +-----------------------------+------------------+---------+