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

Query

SELECT  T.DatabaseName,
        T.TableName AS ProcedureName,
        C.ColumnName AS ParameterName,
        CASE C.SPParameterType
            WHEN 'I' THEN 'IN'
            WHEN 'O' THEN 'OUT'
            WHEN 'B' THEN 'IN/OUT' END as ParameterDirection,
        C.ColumnType AS ParameterType
FROM    DBC.TablesV T
LEFT JOIN   DBC.ColumnsV C
ON  C.DatabaseName = T.DatabaseName
AND C.TableName = T.TableName
WHERE   T.TableKind in ('P', 'E')
AND T.DatabaseName NOT IN ('All', 'Crashdumps', 'DBC', 'dbcmngr', 
    'Default', 'External_AP', 'EXTUSER', 'LockLogShredder', 'PUBLIC',
    'Sys_Calendar', 'SysAdmin', 'SYSBAR', 'SYSJDBC', 'SYSLIB',
    'SystemFe', 'SYSUDTLIB', 'SYSUIF', 'TD_SERVER_DB', 'TDStats',
    'TD_SYSGPL', 'TD_SYSXML', 'TDMaps', 'TDPUSER', 'TDQCD',
    'tdwm', 'SQLJ', 'TD_SYSFNLIB', 'SYSSPATIAL')
ORDER BY    T.DatabaseName,
            ProcedureName;

Columns

• DatabaseName - name of the database
• ProcedureName - name of the procedure
• ParameterName - parameter name
• ParameterDirection -parameter direction (IN, OUT, IN/OUT)
• ParameterType - parameters data type. Some of possible values:
  • CF - Char
  • CV - Varchar
  • D - Decimal
  • DA - Date
  • F - Float
  • I2 - Small Int
  • I - Integer
  • AT - Time
  • TZ - Time with time zone
  • TS - Timestamp
  • SZ - Timestamp with time zone
  • All possible data types (look for Possible Values for ColumnType paragraph)

Rows

• One row - represents one procedure parameter
• Scope of rows: - all parameters for procedures
• Ordered by - database name, procedure name

Sample results