Query below return all stored routines (stored functions and stored procedures) and information about it in MySQL database.

Query

select routine_schema as database_name,
       routine_name,
       routine_type as type,
       data_type as return_type,
       routine_definition as definition
from information_schema.routines
where routine_schema not in ('sys', 'information_schema',
                             'mysql', 'performance_schema')
    -- and r.routine_schema = 'database_name' -- put your database name here
order by routine_schema,
         routine_name;

Columns

• database_name - name of the database (schema)
• routine_name - name of the function/procedure
• type -
  • PROCEDURE
  • FUNCTION
• return_type - for stored functions the return value data type, else this value is empty
• definition - text of the SQL statement executed by the function/procedure

Rows

• One row - represents one function/procedure
• Scope of rows: - all functions/procedures in database
• Ordered by - function/procedure database and name

Sample results