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.