List stored procedures with MS SQL ServerList stored procedures with MS SQL Server

Posted July 30th, 2008 in Microsoft 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!

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.

That wraps up this 3 part series which looked at the sp_tables, sp_columns and sp_stored_procedures stored procedures with Microsoft SQL Server databases.

Recent posts: