Chapter 11: Accessing Catalog Information with CSPs

sp_stored_procedures

Returns a list of available procedures.

sp_stored_procedures [sp_name] [, sp_owner]
 [, sp_qualifier]

sp_name: is the stored procedure name. Use the wildcard character to request information about more than one stored procedure. If left blank, sp_stored_procedures returns information for all procedures.

sp_owner: is the owner of the stored procedure. Use the wildcard character to request information about procedures owned by more than one user.

The DB2 access service selects information from the SYSPROCEDURES table. The cspdb2.sql script creates this table during installation of DirectConnect.
This function corresponds to the ODBC function SQLProcedures.

sp_stored_procedures lists and describes stored procedures. Results are ordered by the following columns:

The lengths for varchar columns shown in the result set tables are maximums; the actual lengths depend on the target database.

The following table describes the result set.

Table 11-15: Result set for *sp_stored_procedures*
Column	Datatype	Description
PROCEDURE_QUALIFIER	varchar(128)	Always NULL.
PROCEDURE_OWNER	varchar(128)	Procedure owner.
PROCEDURE_NAME	varchar(128) NOT NULL	Procedure name.
NUM_INPUT_PARAMS	int NOT NULL	Number of input parameters in the stored procedure. -1 means the number of input parameters is unknown.
NUM_OUTPUT_PARAMS	int NOT NULL	Number of return parameters in the stored procedure. -1 means the number of return parameters is unknown.
NUM_RESULT_SETS	int NOT NULL	Number of result sets returned by the stored procedure. -1 means the number of result sets is unknown.
REMARKS	varchar(254)	A description of the procedure.
PROCEDURE_TYPE	smallint	Defines the procedure type: 0 SQL_PT_UNKNOWN if it cannot be determined whether the procedure returns a value. 1 SQL_PT_PROCEDURE if the returned object is a procedure; it does not have a return value. 2 SQL_PT_FUNCTION if the returned object is a function; it has a return value.