In MariaDB, we can use the SHOW PROCEDURE STATUS command to return a list of stored procedures.

We can also query the information_schema.routines table to do the same thing.

The SHOW PROCEDURE STATUS Command

The easiest way to list out all stored procedures is to use the SHOW PROCEDURE STATUS command.

Simply run the following to list out all stored procedures:

SHOW PROCEDURE STATUS;

The syntax goes like this:

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

So you can use a LIKE clause or WHERE clause to narrow the results down.

Example:

SHOW PROCEDURE STATUS LIKE 'film%';

Result:

+--------+-------------------+-----------+------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db     | Name              | Type      | Definer          | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+--------+-------------------+-----------+------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| sakila | film_in_stock     | PROCEDURE | barney@localhost | 2021-11-13 07:26:47 | 2021-11-13 07:26:47 | DEFINER       |         | utf8mb4              | utf8mb4_general_ci   | utf8mb4_general_ci |
| sakila | film_not_in_stock | PROCEDURE | barney@localhost | 2021-11-13 07:26:47 | 2021-11-13 07:26:47 | DEFINER       |         | utf8mb4              | utf8mb4_general_ci   | utf8mb4_general_ci |
+--------+-------------------+-----------+------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

The information_schema.routines Table

Another way to get a list of stored procedures is to query the information_schema.routines table.

Example:

SELECT 
    routine_schema as "Database",
    routine_name
FROM 
    information_schema.routines
WHERE 
    routine_type = 'PROCEDURE'
ORDER BY 
    routine_schema ASC, 
    routine_name ASC;

Result:

+----------+--------------------+
| Database | routine_name       |
+----------+--------------------+
| mysql    | AddGeometryColumn  |
| mysql    | DropGeometryColumn |
| pethouse | spGetAllPets       |
| pethouse | spGetPetById       |
| sakila   | film_in_stock      |
| sakila   | film_not_in_stock  |
| sakila   | rewards_report     |
+----------+--------------------+

This table also stores information about stored functions. In the above example, I excluded those by using a WHERE clause to return only stored procedures (i.e. objects with a routine_type of PROCEDURE).

To include stored functions we can remove the WHERE clause:

SELECT 
    routine_schema as "Database",
    routine_name,
    routine_type
FROM 
    information_schema.routines
ORDER BY 
    routine_schema ASC, 
    routine_name ASC;

Result:

+----------+----------------------------+--------------+
| Database | routine_name               | routine_type |
+----------+----------------------------+--------------+
| mysql    | AddGeometryColumn          | PROCEDURE    |
| mysql    | DropGeometryColumn         | PROCEDURE    |
| pethouse | spGetAllPets               | PROCEDURE    |
| pethouse | spGetPetById               | PROCEDURE    |
| sakila   | film_in_stock              | PROCEDURE    |
| sakila   | film_not_in_stock          | PROCEDURE    |
| sakila   | get_customer_balance       | FUNCTION     |
| sakila   | inventory_held_by_customer | FUNCTION     |
| sakila   | inventory_in_stock         | FUNCTION     |
| sakila   | rewards_report             | PROCEDURE    |
+----------+----------------------------+--------------+

In this case I also added the routine_type column so that we can distinguish between the procedures and functions.

We can also exclude certain databases from the result if we want:

SELECT 
    routine_schema as "Database",
    routine_name,
    routine_type
FROM 
    information_schema.routines
WHERE 
    routine_schema NOT IN ('sys', 'information_schema', 'mysql', 'performance_schema')
ORDER BY 
    routine_schema ASC, 
    routine_name ASC;

Result:

+----------+----------------------------+--------------+
| Database | routine_name               | routine_type |
+----------+----------------------------+--------------+
| pethouse | spGetAllPets               | PROCEDURE    |
| pethouse | spGetPetById               | PROCEDURE    |
| sakila   | film_in_stock              | PROCEDURE    |
| sakila   | film_not_in_stock          | PROCEDURE    |
| sakila   | get_customer_balance       | FUNCTION     |
| sakila   | inventory_held_by_customer | FUNCTION     |
| sakila   | inventory_in_stock         | FUNCTION     |
| sakila   | rewards_report             | PROCEDURE    |
+----------+----------------------------+--------------+

Or we could narrow it down to a given database:

SELECT 
    routine_schema as "Database",
    routine_name,
    routine_type
FROM 
    information_schema.routines
WHERE 
    routine_schema = 'pethouse'
ORDER BY 
    routine_name ASC;

Result:

+----------+--------------+--------------+
| Database | routine_name | routine_type |
+----------+--------------+--------------+
| pethouse | spGetAllPets | PROCEDURE    |
| pethouse | spGetPetById | PROCEDURE    |
+----------+--------------+--------------+