SHOW PROCEDURES

Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

Syntax

SQL

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

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

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

SHOW PROCEDURES LIKE '%sp%';

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