sp_helptext

Description

Displays the source text of a compiled object.

Syntax

sp_helptext objname [,number]

Parameters

objname: is the name of the compiled object for which the source text is to be displayed. The compiled object must be in the current database.

number: is an integer identifying an individual procedure, when objname represents a group of procedures. This parameter tells sp_helptext to display the source text for a specified procedure in the group.

Views, defaults, and other non-procedural objects are never grouped; use number only for groups of procedures.

Examples

Example 1

Displays the source text of pub_idrule. Since this rule is in the pubs2 database, execute this command from pubs2:

sp_helptext pub_idrule

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

text
------------------------------------
create rule pub_idrule
as @pub_id in ("1389", "0736", "0877", 
    "1622", "1756")
    or @pub_id like "99[0-9][0-9]"

Example 2

Displays the source text of sp_helptext. Since system procedures are stored in sybsystemprocs, execute this command from sybsystemprocs:

sp_helptext sp_helptext

Example 3

Displays the source text of the myproc group behavior where you specify no number argument. The number of the procedure displays beside the text:

sp_helptext myproc

# Lines of Text
---------------
2
number
text
---------------
1
create procedure myproc; as select 1
2
create procedure myproc;2 as select 2
(2 rows affected)

Example 4

Displays the source text of myproc, specifying a procedure in the myproc group but displaying no grouping number.

sp_helptext myproc, 2

# Lines of Text
---------------
1
text
----------------
create procedure myproc;2 as select 2

Usage

sp_helptext prints out the number of rows in syscomments (255 characters long each) that are occupied by the compiled object, followed by the source text of the compiled object.
sp_helptext looks for the source text in the syscomments table in the current database.
You can encrypt the source text with sp_hidetext.
When sp_helptext operates on a group of procedures, it prints the number column from syscomments in addition to the source text.
A System Security Officer can prevent the source text of compiled objects from being displayed to most users who execute sp_helptext. To restrict select permission on the text column of the syscomments table to the object owner or a System Administrator, use sp_configure to set the select on syscomments.text column parameter to 0. This restriction is required to run Adaptive Server in the evaluated configuration. See the System Administration Guide for more information about the evaluated configuration.

Permissions

Any user can execute sp_helptext.