INFORMATION_SCHEMA PARAMETERS

The INFORMATION_SCHEMA PARAMETERS dictionary view provides information about the parameters of stored routines (stored procedures and stored functions) and the return values of stored functions, but not about built-in (native) functions or loaded functions.

The following table describes the fields in the INFORMATION_SCHEMA PARAMETERS dictionary view.

Field	Description
SPECIFIC_CATALOG	The name of the catalog to which the routine that contains the parameter belongs. This value is always def.
SPECIFIC_SCHEMA	The name of the schema (database) to which the routine that contains the parameter belongs.
SPECIFIC_NAME	The name of the routine that contains the parameter.
ORDINAL_POSITION	The position of the parameter in the stored procedure or function. ORDINAL_POSITION is 1, 2, 3, and so on. For stored functions, there is also a row for the function return value. The row that describes the return value has the following characteristics: ORDINAL_POSITION is 0. PARAMETER_NAME and PARAMETER_MODE are NULL.
PARAMETER_MODE	The mode of the parameter. This value can be IN, OUT, or INOUT. For stored function return values, this value is NULL.
PARAMETER_NAME	The name of the parameter. For stored function return values, this value is NULL.
DATA_TYPE	The data type of the parameter. DATA_TYPE contains only the type name, not other information. DTD_IDENTIFIER contains the data type name and other information, such as precision or length.
CHARACTER_MAXIMUM_LENGTH	The maximum length (in characters) of a string parameter.
CHARACTER_OCTET_LENGTH	The maximum length (in bytes) of a string parameter.
NUMERIC_PRECISION	The numeric precision of the parameter.
NUMERIC_SCALE	The numeric scale of the parameter.
DATETIME_PRECISION	The date precision of the parameter.
CHARACTER_SET_NAME	The character set name of the string parameter.
COLLATION_NAME	The collation of the string parameter.
DTD_IDENTIFIER	The details of the data type. DATA_TYPE contains only the type name, not other information. DTD_IDENTIFIER contains the type name and other information, such as precision or length.
ROUTINE_TYPE	The type of the stored routine. PROCEDURE indicates a stored procedure, and FUNCTION indicates a stored function.

Here is an example:

SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
       WHERE SPECIFIC_SCHEMA='test' AND PARAMETER_NAME='c1'\G
*************************** 1. row ***************************
          SPECIFIC_CATALOG: def
           SPECIFIC_SCHEMA: test
             SPECIFIC_NAME: my_func
          ORDINAL_POSITION: 1
            PARAMETER_MODE: IN 
            PARAMETER_NAME: c1
                 DATA_TYPE: char
  CHARACTER_MAXIMUM_LENGTH: 20
    CHARACTER_OCTET_LENGTH: 80
         NUMERIC_PRECISION: NULL
             NUMERIC_SCALE: NULL
        DATETIME_PRECISION: NULL
        CHARACTER_SET_NAME: utf8mb4
            COLLATION_NAME: utf8mb4_general_ci
            DTD_IDENTIFIER: char(20)
              ROUTINE_TYPE: FUNCTION