SYS_ROUTINES

Information on routines (procedures and UDFs)

Column name

Data type

Contents

SPECIFIC_SCHEMA

CHAR(31)

Name of the schema to which the
routine belongs

SPECIFIC_NAME

CHAR(31)

Specific name of the routine

ROUTINE_TYPE

VARCHAR(28)

PROCEDURE	if a procedure
FUNCTION	if a UDF

DATA_TYPE

VARCHAR(24)

Data type of the return value of a UDF

CHARACTER
CHARACTER VARYING
NATIONAL CHAR
NATIONAL CHAR VARYING
REAL
DOUBLE PRECISION
FLOAT
INTEGER
SMALLINT
NUMERIC
DECIMAL
DATE
TIME
TIMESTAMP

NULL value,

if a procedure

CHARACTER_MAXIMUM_LENGTH

SMALLINT

Max. length of the return value in code
units

if data type is CHARACTER,
CHARACTER VARYING,
NATIONAL CHAR or
NATIONAL CHAR VARYING

NULL value in all other cases

NUMERIC_PRECISION

SMALLINT

Total number of significant digits

for numeric data types

NULL value in all other cases

NUMERIC_PRECISION_RADIX

SMALLINT

Radix

for numeric data types

NULL value in all other cases

NUMERIC_SCALE

SMALLINT

Number of digits right of the decimal
point

for exact numeric data types

NULL value in all other cases

DATETIME_PRECISION

SMALLINT

Number of digits right of the decimal
point

for the data types TIME and
TIMESTAMP

NULL value in all other cases

ROUTINE_DEFINITION

VARCHAR(32000)

Text of the routine

SQL_DATA_ACCESS

VARCHAR(17)

CONTAINS SQL

if CONTAINS SQL was specified in
the definition of the routine

READS SQL DATA

if READS SQL DATA was specified
in the definition of the routine

MODIFIES SQL DATA

if MODIFIES SQL DATA was
specified in the definition of the
routine

IS_NULL_CALL

VARCHAR(3)

NO if a UDF

NULL value in all other cases

IS_USER_DEFINED_CAST

VARCHAR(3)

NO if a UDF
NULL value in all other cases

AS_LOCATOR

VARCHAR(3)

NO if a UDF
NULL value in all other cases

Table 124: SYS_ROUTINES view of the SYS_INFO_SCHEMA