sp_stored_procedures Catalog Stored Procedures

Returns a list of stored procedures in the current environment.

Syntax

sp_stored_procedures [procedure_name] [, procedure_owner] [, procedure_qualifier]

where

procedure_name: Is the name of the procedure used to return catalog information. The procedure_name can be a variable character name with a maximum of 42 characters. Wildcard pattern matching is supported.
procedure_owner: Is the name of the owner of the procedure used to return catalog information. The procedure_owner can be a variable character name with a maximum of 30 characters. Wildcard pattern matching is supported. If procedure_owner is not specified, the default procedure visibility rules of the underlying DBMS apply.
In Microsoft SQL Server, if the current user owns a procedure with the specified name, that table's columns are returned. If procedure_owner is not specified and the current user does not own a procedure with the specified procedure_name, this procedure looks for a procedure with the specified procedure_name owned by the database owner. If one exists, that table's columns are returned.
procedure_qualifier: Specifies the name of the procedure qualifier. Various DBMS products support three-part naming for tables (qualifier.owner.name). In Microsoft SQL Server, this column represents the database name. In some products, it represents the server name of the table's database environment.

Remarks

For maximum interoperability, the gateway client should assume only ANSI-standard SQL pattern matching (the % and _ wildcards).

The privilege information about the current user's execute access to a specific stored procedure is not necessarily checked, so access is not guaranteed. Note that only three-part naming is used, so that only local stored procedures, not remote stored procedures (which need four-part naming), are returned when implemented against SQL Server. If the server attribute ACCESSIBLE_SPROC is Y in the results set for sp_server_info, then only stored procedures that can be executed by the current user are returned.

The sp_stored_procedures stored procedure is equivalent to SQLProcedures in ODBC. The results returned are ordered by PROCEDURE_QUALIFIER, PROCEDURE_OWNER, and PROCEDURE_NAME.

This is the results set:



Column	Datatype	Description

PROCEDURE_QUALIFIER	varchar(32)	Is the name of the procedure qualifier. Various DBMS products support three-part naming for tables (qualifier.owner.name). In Microsoft SQL Server, this column represents the database name. In some products, it represents the server name of the table's database environment. This field can be NULL.
PROCEDURE_OWNER	varchar(32)	Is the name of the procedure owner. Various DBMS products support three-part naming for tables (qualifier.owner.name). In Microsoft SQL Server, this column represents the name of the database user that created the table. This field will always return a value.
PROCEDURE_NAME	varchar(41)	Is the name of the procedure. Various DBMS products support three-part naming for tables (qualifier.owner.name). In Microsoft SQL Server, this column represents the procedure name as listed in the sysobjects table. This field will always return a value.
NUM_INPUT_PARAMS	int	Reserved for future use.
NUM_OUTPUT_PARAMS	int	Reserved for future use.
NUM_RESULT_SETS	int	Reserved for future use.
REMARKS	varchar(254)	Is a description of the procedure. SQL Server will not return a value for this column.
PROCEDURE_TYPE	smallint	Is the procedure type. SQL Server 6.0 always returns 2.0. Can be: 0 SQL_PT_UNKNOWN 1 SQL_PT_PROCEDURE 2 SQL_PT_FUNCTION