Querying the mysql.proc Table

Execute the following query:

SELECT name FROM mysql.proc
WHERE db = 'your_database_name' AND type = 'PROCEDURE';

Replace ‘your_database_name’ with the name of your database.

This query directly queries the mysql.proc table to retrieve the names of all stored procedures in the specified database.

Example:

List all the stored procedures from mydb database:

SELECT name FROM mysql.proc
WHERE db = 'mydb' AND type = 'PROCEDURE';

Output:

Explanation:

• SELECT name: This clause specifies the column(s) that you want to retrieve from the result set. In this case, it selects the name column, which contains the names of stored procedures.
• FROM mysql.proc: This specifies the source table from which the data will be retrieved. The mysql.proc table is a system table in MySQL and MariaDB that stores information about stored procedures and functions.
• WHERE db = ‘mydb’: This is an optional clause which allows for end-users to filter the results fetching from a specific database (DB column). This ensures that only stored procedures belonging to the database ‘mydb’ are selected.
• AND type = ‘PROCEDURE’: This condition further filters the rows based on the value of the type column. It restricts the result set to only include rows where the type column has the value ‘PROCEDURE’. This ensures that only stored procedures are selected.

When working with MariaDB, it’s important to be able to manage and maintain stored procedures effectively. Listing all stored procedures in a database can provide valuable insights into its structure and functionality.

In this article, we’ll explore how to list all stored procedures in MariaDB by understanding the various approaches along with the examples and so on.