Edit

sp_stored_procedures (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Returns a list of stored procedures in the current environment.

Transact-SQL syntax conventions

Syntax

sp_stored_procedures
    [ [ @sp_name = ] N'sp_name' ]
    [ , [ @sp_owner = ] N'sp_owner' ]
    [ , [ @sp_qualifier = ] N'sp_qualifier' ]
    [ , [ @fUsePattern = ] fUsePattern ]
[ ; ]

Arguments

[ @sp_name = ] N'sp_name'

The name of the procedure used to return catalog information. @sp_name is nvarchar(390), with a default of NULL. Wildcard pattern matching is supported.

[ @sp_owner = ] N'sp_owner'

The name of the schema to which the procedure belongs. @sp_owner is nvarchar(384), with a default of NULL. Wildcard pattern matching is supported. If @sp_owner isn't specified, the default procedure visibility rules of the underlying database management system (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.

[ @sp_qualifier = ] N'sp_qualifier'

The name of the procedure qualifier. @sp_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, @sp_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 ([ and ]) 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 set

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 doesn't 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 options:

0 = SQL_PT_UNKNOWN
1 = SQL_PT_PROCEDURE
2 = SQL_PT_FUNCTION

Remarks

For maximum interoperability, the gateway client should assume only SQL standard pattern matching, namely the percent (%) and underscore (_) wildcard characters.

The permission information about execute access to a specific stored procedure for the current user isn't necessarily checked; therefore, access isn't guaranteed. 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're 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. Return all stored procedures in the current database

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

USE AdventureWorks2022;
GO
EXEC sp_stored_procedures;

B. Return a single stored procedure

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

USE AdventureWorks2022;
GO

sp_stored_procedures N'uspLogError',
    N'dbo',
    N'AdventureWorks2022',
    1;

Related content