Displaying Information About Stored Procedures

Several system procedures provide information from the system tables about stored procedures.

You can use the sp_help system procedure to get a report on a stored procedure. For example, you can get information on the stored procedure byroyalty, which is part of the pubs database, like this:

sp_help byroyalty
Name
Owner
Type
------------
---------
-----------------
byroyalty
dbo
stored procedure


Data_located_on_segment
When_created
-----------------------
---------------------
not applicable
Jul 25 1995 3:56PM


Parameter_name
Type
Length
Prec
Scale
Param_order
--------------
---------
---------
-----
------
-----------
@percentage
int
4
10
0
1


The sp_depends system procedure lists stored procedures and objects referenced by those procedures. If you specify an object, sp_depends lists all the stored procedures that reference that object. If you specify a stored procedure name, sp_depends lists all the objects referenced by that stored procedure. The following statement lists all the objects referenced by the user-created stored procedure byroyalty:

sp_depends byroyalty
In the current database the specified object references the following:
name
type
updated
selected
----------------
------------
--------
--------
dbo.titleauthor
user table
no
no

(1 row(s) affected)