Chapter 1: System Procedures

sp_helptext

Description

Displays the source text of a compiled object. Displays the source text of computed columns or function-based index definitions.

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 truncates trailing spaces when displaying the source text from syscomments
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.
The source-text is displayed using char(255), so trailing spaces are present in the displayed text. The text stored in syscomments may not include these trailing spaces. syscomments stores the text "as supplied," so another application or tool may not have included these trailing spaces. Because of this, you should not use sp_helptext to get a copy of the text stored. Instead, use other tools like defncopy.
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.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event	Audit option	Command or access audited	Information in extrainfo
38	exec_procedure	Execution of a procedure	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect