Displays the source text of a compiled object, as well as the text for user-defined functions, computed columns, or function-based index definitions.
sp_helptext objname[,grouping_num][, numlines[, printopts]]]
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.
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.
This parameter also specifies the start line number from which to generate the SQL text, when the printops argument is used.
Views, defaults, and other non-procedural objects are never grouped; use number only for groups of procedures.
specifies the numbers of lines for which to generate SQL text. If the argument printopts is also used with showsql, numlines specifies the number of lines of SQL text to display; if printopts is used with context, numlines is treated as the context block width surrounding the starting line number.
supports various comma-separated properties of the output format. One or more of these print options can be specified, in any order, as a comma-separated string:
showsql – generates formatted SQL output for the compiled object. If showsql does not appear in the printopts list, this property is not invoked.
linenumbers – produces line numbers for each line of SQL output.
comments – produces
the line numbers as a comment field (/*<nnn>*/
),
so that the generated SQL can still recreate the compiled object,
without furter edits, if necessary.
context – produces a context block of output around a specified starting line number. If no, or null, numlines parameter is called, a default context block of five lines, generated before and after the line number of interest, is supplied.
noparams – suppresses the automatically generated parameter information. Use this print option to produce only the relevant portion of SQL output for the compiled object.
ddlgen – generates the SQL text as a DDL script, prefacing the output with a use database command and a drop object command. This allows you to reproduce almost exactly the SQL required to recreate most compiled objects, such as procedures, triggers, views, defaults, and rules.
The print options ddlgen and context are mutually exclusive specifiers. Used together, they raise an error. To get line numbers when you are displaying a context block of SQL text, use the context and linenumbers specifiers.
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]"
Displays the source text of sp_helptext. Since system procedures are stored in sybsystemprocs, execute this command from sybsystemprocs:
sp_helptext sp_helptext
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)
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
Generates text for sp_help:
sp_helptext sp_help,NULL,NULLM 'showsql'
To generate text for sp_help, producing line numbers:
sp_helptext sp_help, NULL,NULL,'showsql,linenumbers'
To generate the text for sp_help, in a context block of seven lines starting at line 25, with output generated in a comment block:
sp_helptext sp_help,25,7,'showsql,comments,context'
Generates the text for sp_droptabledef, producing the output as a stand-alone DDL script that you can use to recreate the procedure:
sp_helptext sp_droptabledef,NULL,NULL,'showsql,ddlgen' ------------- use sybsystemprocs
------------- IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'sp_droptabledef' AND type = 'P' DROP PROCEDURE sp_droptabledef -------------- /*Sccsud="%Z%generic/sproc/src/%M%%I%%G%"*/ /* **Omni only */ create procedure sp_droptabledef @tablename varchar(92) /*tablename*/ as begin declare @status int exec @status = sp_dropobjectdef @tablename return(@status) end ---------- (return status = 0)
Uses sp_helptext on a view created with delimited identifiers. You do not need set quoted_identifier on to extract the SQL defining the view. You do need it ON to create objects using delimited identifiers.
set quoted_identifier ON --------- create table "t one" (c1 int, "c two" varchar(10), "c three int) --------- create table "t two" ("t2 one" int, "t2 two" varchar(10), t2_three int) ------------ create view "v one" as select * from "t one" UNION select "t2 one","t2 two",t2_three from "t two" -----------------
Uses sp_helptext on one of its subprocedures, sp_showtext_output, to identify the context of SQL source code surrounding line 813:
sp_helptext sp_showtext_output, 813,NULL,'context,linenumbers,showsql'
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.
Even when you use sp_helptext in ddlgen mode, the showsql print option is required.
The object whose text you want to retrieve must reside in the database where the procedure is executed.
If the text is either hidden or not in syscomments, an error message is raised. If, however, you request a context block output, and the text is missing or hidden, a message reporting the missing text is printed, but no error is raised.
Text generated using the ddlgen print option may still fail to create a compiled object correctly if it contains references to other objects, such as temporary tables, that do not already exist when the generated script is executed.
If the compiled object contains a select
*
statement,
it usually reflects the entire column list of the table this statement
references.
You can generate SQL text for compiled objects created
with quoted identifiers, but if the compiled object contains a select *
statement,
the expanded column list appears with bracketed identifiers after
Adaptive Server writes the text to syscomments.
For example:
[this column], [column name with space]
It is not necessary to set quoted_identifier ON when generating text for compiled objects that are themselves, or use, delimited identifiers.
Any user can execute sp_helptext.
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 |
|
System procedures sp_checksource, sp_configure, sp_hidetext