Returns a list of stored procedures in the current environment.
sp_stored_procedures [[@sp_name =] 'name']
[,[@sp_owner =] 'owner']
[,[@sp_qualifier =] 'qualifier']
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.
None
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 1 = SQL_PT_PROCEDURE 2 = SQL_PT_FUNCTION |
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.