Transact-SQL User's Guide

Getting Information About Stored Procedures

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

System procedures are briefly discussed in "System Procedures". For complete information about system procedures, see Chapter 3, "System Procedures," in the Adaptive Server Reference Manual.

Getting a Report with sp_help

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

sp_help byroyalty

Name       Owner   type
--------   ------
----------------
byroyalty  dbo     stored
procedure

Data_located_on_segment         When_created
    
---------------------------     -------------------- 
not
applicable                  Jul 10, 1997 4:57PM
 
Parameter_name
Type    Length   Prec  Scale  Param_order 
-------------- ------
------   ----  -----  ----------- 
@percentage    int          4
NULL   NULL            1 
 
(return status = 0)

You can get help on a system procedure by executing sp_help when using the sybsystemprocs database.

Viewing the Source Text of a Procedure with sp_helptext

To display the source text of the create procedure statement, execute the system procedure sp_helptext:

sp_helptext
byroyalty 

# Lines of Text
---------------

1
 
(1 row affected) 
 
text

--------------------------------------------------- 
create
procedure byroyalty @percentage int 
as 
select au_id from
titleauthor 
where titleauthor.royaltyper = @percentage 
 
(1
row affected, return status = 0)

You can view the source text of a system procedure by executing sp_helptext when using the sybsystemprocs database.

If the source text of a stored procedure was encrypted using the system procedure sp_hidetext, Adaptive Server displays a message advising you that the text is hidden. For information about hiding source text, see sp_hidetext in the Adaptive Server Reference Manual.

Identifying Dependent Objects with sp_depends

The system procedure sp_depends lists all the stored procedures that reference the object you specify or all the procedures that it is dependent upon.

For example, this command lists all the objects referenced by the user-created stored procedure byroyalty:

sp_depends byroyalty 

Things the
object references in the current database. 
object           type
 updated      selected 
---------------- ----------- ---------
-------- 
dbo.titleauthor  user table  no          no
 
(return
status = 0)

The following statement uses sp_depends to list all the objects that reference the table titleauthor:

sp_depends titleauthor 

Things inside the current
database that reference the object. 
 
object          type

--------------  ------------------ 
dbo.byroyalty   stored
procedure
dbo.titleview   view 
 
(return status =
0)

You must drop and re-create the procedure if any of its referenced objects have been renamed.

Identifying Permissions with sp_helprotect

The system procedure sp_helprotect reports permissions on a stored procedure (or any other database object). For example:

sp_helprotect byroyalty

grantor    grantee
type  action    object    column  grantable
---------  ---------
----  --------- -------   -----   ---------
dbo        public
Grant Execute   byroyalty All     FALSE
 
(return status = 0)