Technical Article

How to get list of Stored Procedure

,

You can use following stored procedure with four optional input parameters to get a list of all stored procedures in the current environment.

EXEC SP_STORED_PROCEDURES @sp_name = 'procedure name'
, @sp_owner = 'schema name'
, @sp_qualifier = 'database name'
, @fUsePattern = 'fUsePattern'

Note:
1. All parameters are optional.
2. @sp_name and @sp_owner support wildcard pattern matching (underscore "_", percent "%" and brackets []).
3. @sp_qualifier it will have null or current database name only.
4. @fUsePattern, it can be 0 (wildcard pattern matching is off) or 1 (wildcard pattern matching is on), by default it is 1.

Examples:
1. To see complete list of all stored procedures in current database
EXEC SP_STORED_PROCEDURES
2. To see complete list of procedures, procedure name starting with "fn" characters
EXEC SP_STORED_PROCEDURES @sp_name = 'fn%'
3. To see complete list of procedure, procedure name starting with "fn" and schema name starting with "s" characters
EXEC SP_STORED_PROCEDURES @sp_name = 'fn%', @sp_owner = 's%'

 

  

Rate

5 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (1)

You rated this post out of 5. Change rating