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]]]
This parameter also specifies the start line number from which to generate the SQL text, when the printops argument is used.
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.
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]"
sp_helptext sp_helptext
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)
sp_helptext myproc, 2
# Lines of Text --------------- 1 text ---------------- create procedure myproc;2 as select 2
sp_helptext sp_help,NULL,NULLM 'showsql'
sp_helptext sp_help, NULL,NULL,'showsql,linenumbers'
sp_helptext sp_help,25,7,'showsql,comments,context'
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)
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" -----------------
sp_helptext pred1
# Lines of Text --------------- 1 text ---------------------------------------------------- grant select on tab1 where col1 = 5 as pred1 to robert
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 SAP ASE 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 with text that 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 the SAP ASE 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.
The permission checks for sp_helptext differ based on your granular permissions settings.
Setting | Description |
---|---|
Enabled | With granular permissions enabled, you must be the object owner, the database owner, or a user with own database privilege. |
Disabled | With granular permissions disabled, you must be the object owner, database owner, or a user with sa_role. |
Values in event and extrainfo columns from the sysaudits table are:
Information | Values |
---|---|
Event | 38 |
Audit option | exec_procedure |
Command or access audited | Execution of a procedure |
Information in extrainfo |
|