List stored procedures with MS SQL Server

This is the third and final post in a series about using the sp_tables, sp_columns and sp_stored_procedures stored procedures with Microsoft SQL Server databases. 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!

@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":