How to Show Procedures and Functions in MySQL Using the Command Line

MySQL offers powerful features for managing and querying databases, including the ability to create stored procedures and functions. These objects allow you to encapsulate logic in reusable components, making your database operations more efficient. If you’re working in a MySQL environment and want to list all the stored procedures and functions in your database, the command line is your go-to tool. In this blog post, we’ll guide you through the process of displaying stored procedures and functions in MySQL.

Prerequisites

Before doing anything, make sure you have the following:

1. Access to the MySQL command line (via a terminal or command prompt).
2. Credentials for a MySQL user account with sufficient privileges to view procedures and functions.
3. Basic knowledge of MySQL syntax.

Viewing Stored Procedures and Functions

MySQL does not have a single command like SHOW PROCEDURES or SHOW FUNCTIONS. Instead, you can use the INFORMATION_SCHEMA tables to retrieve information about stored procedures and functions.

1. List All Procedures

To list all stored procedures in the current database, you can query the ROUTINES table in the INFORMATION_SCHEMA database:

SELECT ROUTINE_NAME, ROUTINE_TYPE, CREATED, LAST_ALTERED
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = ‘PROCEDURE’ AND ROUTINE_SCHEMA = ‘your_database_name’;

Replace your_database_name with the name of your database. This query will return:

• ROUTINE_NAME: The name of the procedure.
• ROUTINE_TYPE: The type of routine (in this case, PROCEDURE).
• CREATED: The date and time the procedure was created.
• LAST_ALTERED: The last modification date and time.

2. List All Functions

To list all stored functions in the current database, modify the query slightly to filter by FUNCTION:

SELECT ROUTINE_NAME, ROUTINE_TYPE, CREATED, LAST_ALTERED
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = ‘FUNCTION’ AND ROUTINE_SCHEMA = ‘your_database_name’;

This query retrieves information similar to the one for procedures but focuses on functions.

3. Combined List of Procedures and Functions

To retrieve both procedures and functions in a single query, omit the ROUTINE_TYPE filter:

SELECT ROUTINE_NAME, ROUTINE_TYPE, CREATED, LAST_ALTERED
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = ‘your_database_name’;

This query will return all routines (both procedures and functions) within the specified database.

Also,

Here are some important things to keep in mind:

• Access Privileges: You need appropriate privileges to view the INFORMATION_SCHEMA tables and routines. For example, the SHOW ROUTINE privilege is required.
• Filtering by Routine Schema: Always include the ROUTINE_SCHEMA filter to avoid listing routines from all databases, especially on shared environments.
• Sorting: You can add an ORDER BY clause to sort the results by name, creation date, or any other column.

Example:

SELECT ROUTINE_NAME, ROUTINE_TYPE, CREATED, LAST_ALTERED
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = ‘your_database_name’
ORDER BY CREATED DESC;

Bonus Tip: Describing a Specific Procedure or Function

Once you have identified a procedure or function, you might want to see its details. Use the SHOW CREATE PROCEDURE or SHOW CREATE FUNCTION command:

For a procedure:

SHOW CREATE PROCEDURE your_database_name.procedure_name;

For a function:

SHOW CREATE FUNCTION your_database_name.function_name;

These commands display the SQL code used to create the specified procedure or function.

Managing stored procedures and functions in MySQL is straightforward once you know how to leverage the INFORMATION_SCHEMA tables and the SHOW CREATE commands. These tools allow you to keep track of your database objects and ensure your database logic remains well-organized.

By using these queries, you can effectively list and manage your stored routines, enabling better database administration and development workflows. If you’re looking to deepen your knowledge of MySQL, understanding these fundamental commands is an excellent place to start.