sp_stored_procedures (Transact-SQL)
Returns a list of stored procedures in the current environment.
.gif "Topic link icon")
Syntax
sp_stored_procedures [ [ @sp_name = ] 'name' ]
[ , [ @sp_owner = ] 'schema']
[ , [ @sp_qualifier = ] 'qualifier' ]
[ , [@fUsePattern = ] 'fUsePattern' ]
Arguments
[ @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 = ] 'schema'
Is the name of the schema to which the procedure belongs. schema 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 SQL Server, if the current schema contains a procedure with the specified name, that procedure is returned. If a nonqualified stored procedure is specified, the Database Engine searches for the procedure in the following order:
The sys schema of the current database.
The caller's default schema if executed in a batch or in dynamic SQL; or, if the non-qualified procedure name appears inside the body of another procedure definition, the schema containing this other procedure is searched next. For more information about default schemas, see User-Schema Separation.
The dbo schema in the current database.
[ @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 in the form (qualifier**.schema.**name. In SQL Server, qualifier represents the database name. In some products, it represents the server name of the database environment of the table.[ @fUsePattern = ] 'fUsePattern'
Determines whether the underscore (_), percent (%), or brackets [ ]) are interpreted as wildcard characters. fUsePattern is bit, with a default of 1.0 = Pattern matching is off.
1 = Pattern matching is on.
Return Code Values
None
Result Sets
Column name |
Data type |
Description |
|---|---|---|
PROCEDURE_QUALIFIER |
sysname |
Procedure qualifier name. This column can be NULL. |
PROCEDURE_OWNER |
sysname |
Procedure owner name. This column always returns a value. |
PROCEDURE_NAME |
nvarchar(134) |
Procedure name. This column 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. This value can be one of the following: 0 = SQL_PT_UNKNOWN 1 = SQL_PT_PROCEDURE 2 = SQL_PT_FUNCTION |
Remarks
For maximum interoperability, the gateway client should assume only SQL-92-standard pattern matching (the percent (%) and underscore (_) wildcard characters).
The permission information about execute access to a specific stored procedure for the current user is not necessarily checked; therefore, access is not guaranteed. Note that only three-part naming is used. This means that only local stored procedures, not remote stored procedures (which require four-part naming), are returned when they are executed 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.
Permissions
Requires SELECT permission on the schema.
Examples
A. Returning all stored procedures in the current database
The following example returns all stored procedures in the AdventureWorks2008R2 database.
USE AdventureWorks2008R2;
GO
EXECUTE sp_stored_procedures;
B. Returning a single stored procedure
The following example returns a result set for the uspLogError stored procedure.
USE AdventureWorks2008R2;
GO
EXECUTE sp_stored_procedures N'uspLogError', N'dbo', N'AdventureWorks2008R2', 1;