TechNet
Export (0) Print
Expand All

sp_stored_procedures (Transact-SQL)

 

Updated: June 10, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Returns a list of stored procedures in the current environment.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

Topic link icon Transact-SQL Syntax Conventions

  
sp_stored_procedures [ [ @sp_name = ] 'name' ]   
    [ , [ @sp_owner = ] 'schema']   
    [ , [ @sp_qualifier = ] 'qualifier' ]  
    [ , [@fUsePattern = ] 'fUsePattern' ]  

[ @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.

  • 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.

None

Column nameData typeDescription
PROCEDURE_QUALIFIERsysnameProcedure qualifier name. This column can be NULL.
PROCEDURE_OWNERsysnameProcedure owner name. This column always returns a value.
PROCEDURE_NAMEnvarchar(134)Procedure name. This column always returns a value.
NUM_INPUT_PARAMSintReserved for future use.
NUM_OUTPUT_PARAMSintReserved for future use.
NUM_RESULT_SETSintReserved for future use.
REMARKSvarchar(254)Description of the procedure. SQL Server does not return a value for this column.
PROCEDURE_TYPEsmallintProcedure 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

For maximum interoperability, the gateway client should assume only SQL 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.

Requires SELECT permission on the schema.

A. Returning all stored procedures in the current database

The following example returns all stored procedures in the AdventureWorks2012 database.

USE AdventureWorks2012;  
GO  
EXEC sp_stored_procedures;  

B. Returning a single stored procedure

The following example returns a result set for the uspLogError stored procedure.

USE AdventureWorks2012;  
GO  
sp_stored_procedures N'uspLogError', N'dbo', N'AdventureWorks2012', 1;  

Catalog Stored Procedures (Transact-SQL)
System Stored Procedures (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft