How to get List of stored procedures in SQL Server

This post is about sp_stored_procedures which is used to get a list of stored procedures in a SQL Server database.

The simplest way to use sp_stored_procedures is to call it with no arguments:

exec sp_stored_procedures

This will return a complete list of stored procedures for the MS SQL Server database use are currently using. Example output from the above is as follows:

PROCEDURE_QUALIFIER	PROCEDURE_OWNER	PROCEDURE_NAME	NUM_INPUT_PARAMS	NUM_OUTPUT_PARAMS	NUM_RESULT_SETS	REMARKS	PROCEDURE_TYPE
MyDb	dbo	Foo;1	-1	-1	-1		2
MyDb	dbo	Bar;1	-1	-1	-1		2

The PROCEDURE_QUALIFIER column indicates which database the stored procedure belongs to; PROCEDURE_OWNER is the owner; PROCEDURE_NAME is the name of the stored procedure; NUM_INPUT_PARAMS, NUM_OUTPUT_PARAMS and NUM_RESULT_SETS are reserved for future use, according to the MSDN documentation; REMARKS always returns empty; PROCEDURE_TYPE always returns 2.

So as you can see most of the data returned is fairly useless, being reserved for "future use" or always returning the same value. However getting the list of procedure names is of course useful!

The sp_stored_procedures stored procedure also takes 4 optional parameters:

@sp_name - if specified it will return just the stored procedure name specified. Wildcard pattern matching with _ [ ] and % is supported, so you can do the following, for example, to just return stored procedures starting with "a":

exec sp_stored_procedures 'a%'
OR
exec sp_stored_procedures @sp_name = 'a%'

@sp_owner - allows you to list stored procedures only belonging to a particular owner. As with the @sp_name parameter it supports wildcard matching with % [ ] and _

@qualifier - allows you to specify which database to list stored procedures for.

@fUsePattern - specifies whether wildcard matching is on or off. If set to 1 then wildcard matching is on (the default) and if 0 then wildcard matching is switched off.

Source:http://www.tutorialsdesk.com/2012/09/list-stored-procedures-with-ms-sql.html