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%'

sp_msforeachdb: Improving on an Undocumented Stored Procedure

by Edward Pollack

SQLServerCentral.com

sp_msforeachdb

Have you ever wanted to run a query across every database on a server with the convenience of a stored procedure? If so, Microsoft provided a stored procedure to do so. It’s unreliable, outdated, and somewhat obfuscated, though. Let’s improve on it!