Home

	Expand Search

Transact-SQL User's Guide

Chapter 14 Using Stored Procedures

Getting information about stored procedures

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 the Reference Manual.

Getting a report with sp_help

You can get a report on a stored procedure using 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 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 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 Reference Manual.

Identifying dependent objects with sp_depends

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

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)