List stored procedure parameters in MariaDB database

Query below return all parameters of stored routines (stored functions and stored procedures) and details about it in MariaDB 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

0
There are no comments. Click here to write the first comment.