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

Query

select r.routine_schema as database_name,
       r.specific_name as routine_name,
       r.routine_type AS type,
       p.parameter_name,
       p.data_type,
       case when p.parameter_mode is null and p.data_type is not null
            then 'RETURN'
            else parameter_mode end as parameter_mode,
       p.character_maximum_length as char_length,
       p.numeric_precision,
       p.numeric_scale
from information_schema.routines r
left join information_schema.parameters p
          on p.specific_schema = r.routine_schema
          and p.specific_name = r.specific_name
where r.routine_schema not in ('sys', 'information_schema',
                               'mysql', 'performance_schema')
    -- and r.routine_schema = 'database_name' -- put your database name here
order by r.routine_schema,
         r.specific_name,
         p.ordinal_position;

Columns

• database_name - name of the database (schema) conaining routine
• routine_name - name of the function/procedure
• type -
  • PROCEDURE
  • FUNCTION
• parameter_name - parameter name
• data_type - data type of parameter
• parameter_mode - mode of the parameter
  • IN
  • OUT
  • INOUT
  • RETURN - only for stored functions return value
  • null value only for procedures without parameters
• char_length - maximum length of a string
• numeric_precision - precision of numeric type
• numeric_scale - scale of numeric data type

Rows

• One row - represents one function/procedure parameter
• Scope of rows: - all parameters for routines
• Ordered by - database name, specific name, parameter position

Sample results