List stored procedures and functions in MariaDB database

Query below return all stored routines (stored functions and stored procedures) and information about it in MariaDB 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)
  • specific_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

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