Important

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

SHOW PROCEDURES

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.
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:

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

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.

SQL

SHOW PROCEDURES;

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