How to List all Stored Procedures in MariaDB?

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.

How to List all Stored Procedures in MariaDB

When working with a MariaDB database, you may need to list all the stored procedures it contains. This can be useful for various purposes, such as documentation, debugging, or understanding the database's functionality. MariaDB offers several methods that help us to list all stored procedures in MariaDB as follows:

1. Using Information Schema
   
2. Using the SHOW PROCEDURE STATUS Command
   
3. Querying the mysql.proc Table

1. Using the Information Schema

We can 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.

To connect to the MariaDB server, use the client or the command-line interface that we are comfortable with. Once connected, execute the following SQL query:

Syntax:

SELECT specific_name FROM information_schema.routines
WHERE routine_type = 'PROCEDURE' AND routine_schema = 'your_database_name';

This query retrieves the names of all stored procedures within the specified database.

Example:

Suppose I have a database named as mydb and I have to list the all stored procedures in that database then I will execute the below command:

SELECT specific_name FROM information_schema.routines
WHERE routine_type = 'PROCEDURE' AND routine_schema = 'mydb';

Output:

Explanation:

• SELECT specific_name: The clause specifies the column(s) that you desire to get from the results set. The example selects specific_name column which stores the names of stored procedures.
  
• FROM information_schema.routines: It indicates the table name from where the data will be fetched. The information_schema.routines table is a system view in MariaDB that stores data on stored routines (procedures and functions).
  
• WHERE routine_type = 'PROCEDURE': This is a condition adding to the WHERE clause to only retrieve rows from the information_schema.routines table that matches the specified condition. It limits rows to those that have the routine_type column with the value 'PROCEDURE'. Only these stored procedures will be able to run.
  
• AND routine_schema = 'mydb': This condition further filters the rows based on the value of the routine_schema column. It restricts the result set to only include stored procedures that belong to the database schema named 'mydb'.

2. Using the SHOW PROCEDURE STATUS Command

We can list all the procedures by the use of SHOW PROCEDURE STATUS command. This command lists all stored procedures in the specified database along with additional information such as the name, type, and creation time. It is an easiest way to list out all stored procedures.

Syntax:

SHOW PROCEDURE STATUS WHERE Db = 'your_database_name';

Example:

I have to list all the stored procedures in my database:

SHOW PROCEDURE STATUS WHERE Db = 'mydb';

Output:

Explanation:

• SHOW PROCEDURE STATUS: This is the main part of the statement. It indicates that we want to display information about stored procedures.
  
• WHERE Db = 'mydb': This is an optional clause which allows for end-users to filter the results fetching from a specific database (DB column). It restricts the output to only show information about stored procedures in the database with the name 'mydb'

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

Conclusion

All stored procedures of MariaDB is crucial for database management tasks and general maintenance issues. No matter if you are using the information schema for querying or the command like SHOW PROCEDURE STATUS directly or even going into the system tables like mysql.proc, MariaDB provides you with several options to reach your goal quickly and efficiently.