sp_helptext

Displays the source text of a compiled object, as well as the text for user-defined functions, computed columns, or function-based index definitions.

Syntax

sp_helptext objname[,grouping_num][, numlines[, printopts]]]

Parameters

Examples

Usage

There are additional considerations when using sp_helptext:
  • 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.

Permissions

The permission checks for sp_helptext differ based on your granular permissions settings.

SettingDescription
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.

Auditing

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

InformationValues
Event

38

Audit option

exec_procedure

Command or access audited

Execution of a procedure

Information in extrainfo
  • 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

Related reference
sp_checksource
sp_configure
sp_hidetext