Returns a list of stored procedures in the current environment.
sp_stored_procedures [[@sp_name =] 'name']
[,[@sp_owner =] 'owner']
[,[@sp_qualifier =] 'qualifier']
[@sp_name =] 'name'
Is the name of the procedure used to return catalog information. name is nvarchar(390), with a default of NULL. Wildcard pattern matching is supported.
[@sp_owner =] 'owner'
Is the name of the owner of the procedure. owner is nvarchar(384), with a default of NULL. Wildcard pattern matching is supported. If 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 procedure is returned. If owner is not specified and the current user does not own a procedure with the specified name, this procedure looks for a procedure with the specified name owned by the database owner. If one exists, that procedure is returned.
[@qualifier =] 'qualifier'
Is the name of the procedure qualifier. qualifier is sysname, with a default of NULL. Various DBMS products support three-part naming for tables (qualifier.owner.name). In SQL Server, this column represents the database name. In some products, it represents the server name of the table's database environment.
Return Code Values
|Column name||Data type||Description|
|PROCEDURE_QUALIFIER||sysname||Procedure qualifier name. This field can be NULL.|
|PROCEDURE_OWNER||sysname||Procedure owner name. This field always returns a value.|
|PROCEDURE_NAME||nvarchar(134)||Procedure name. This field always returns 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)||Description of the procedure. SQL Server does not return a value for this column.|
|PROCEDURE_TYPE||smallint||Procedure type. SQL Server always returns 2.0. Can be:
0 = SQL_PT_UNKNOWN
For maximum interoperability, the gateway client should assume only SQL-92-standard 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 result set for sp_server_info, only stored procedures that can be executed by the current user are returned.
sp_stored_procedures is equivalent to SQLProcedures in ODBC. The results returned are ordered by PROCEDURE_QUALIFIER, PROCEDURE_OWNER, and PROCEDURE_NAME.
Execute permissions default to the public role.