How to list all stored procedures in MariaDB

This article discusses two methods of listing all stored procedures in MariaDB.

Posted on

In MariaDB, we can get all stored procedures using two methods/commands:

  1. Use the SHOW PROCEDURE STATUS command to show all stored procedures.
  2. Query a list of stored procedures from the information_schema.routines table.

Use SHOW PROCEDURE STATUS

Just run the following SHOW PROCEDURE STATUS command to list all stored procedures:

SHOW PROCEDURE STATUS;

If you need to filter some stored procedures, please use the LIKE clause or WHERE clause, as follows:

SHOW PROCEDURE STATUS
    [LIKE 'pattern' | WHERE expr]

Example

To view all stored procedures in the Sakila sample database, use the following statement with the WHERE clause:

SHOW PROCEDURE STATUS WHERE db = 'sakila';

Output:

+--------+-------------------+-----------+----------------+---------------------+---------------------+---------------+--------------------------------------------------+----------------------+----------------------+--------------------+
| Db     | Name              | Type      | Definer        | Modified            | Created             | Security_type | Comment                                          | character_set_client | collation_connection | Database Collation |
+--------+-------------------+-----------+----------------+---------------------+---------------------+---------------+--------------------------------------------------+----------------------+----------------------+--------------------+
| sakila | film_in_stock     | PROCEDURE | root@localhost | 2022-12-12 15:07:32 | 2022-12-12 15:07:32 | DEFINER       |                                                  | utf8mb4              | utf8mb4_general_ci   | utf8mb4_general_ci |
| sakila | film_not_in_stock | PROCEDURE | root@localhost | 2022-12-12 15:07:32 | 2022-12-12 15:07:32 | DEFINER       |                                                  | utf8mb4              | utf8mb4_general_ci   | utf8mb4_general_ci |
| sakila | rewards_report    | PROCEDURE | root@localhost | 2022-12-12 15:07:32 | 2022-12-12 15:07:32 | DEFINER       | Provides a customizable report on best customers | utf8mb4              | utf8mb4_general_ci   | utf8mb4_general_ci |
+--------+-------------------+-----------+----------------+---------------------+---------------------+---------------+--------------------------------------------------+----------------------+----------------------+--------------------+
3 rows in set (0.014 sec)

To view all stored procedures whose names begin with film, use the following statement with the LIKE clause:

SHOW PROCEDURE STATUS LIKE 'film%';

Output:

+--------+-------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db     | Name              | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+--------+-------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| sakila | film_in_stock     | PROCEDURE | root@localhost | 2022-12-12 15:07:32 | 2022-12-12 15:07:32 | DEFINER       |         | utf8mb4              | utf8mb4_general_ci   | utf8mb4_general_ci |
| sakila | film_not_in_stock | PROCEDURE | root@localhost | 2022-12-12 15:07:32 | 2022-12-12 15:07:32 | DEFINER       |         | utf8mb4              | utf8mb4_general_ci   | utf8mb4_general_ci |
+--------+-------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
2 rows in set (0.015 sec)

Use the information_schema.routines table

You can also get a list of stored procedures from the information_schema.routines table. information_schema.routines is a system table that stores information about stored procedures and functions in the database.

You need specify the type routine_type to query:

  • PROCEDURE: stored procedure
  • FUNCTION: function

For example, to query all stored procedures in the Sakila sample database, use the following statement:

SELECT
  routine_schema,
  routine_name
FROM
  information_schema.routines
WHERE routine_schema = 'sakila'
  AND routine_type = 'PROCEDURE';

Output:

+----------------+-------------------+
| routine_schema | routine_name      |
+----------------+-------------------+
| sakila         | film_in_stock     |
| sakila         | film_not_in_stock |
| sakila         | rewards_report    |
+----------------+-------------------+
3 rows in set (0.006 sec)

Conclusion

This article discusses two ways to list all stored procedures in MariaDB.