sp_helptext

Description

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

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.

grouping_num

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.

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

numlines

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.

printopts

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.

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

Example 5

Generates text for sp_help:

sp_helptext sp_help,NULL,NULLM 'showsql'

Example 6

To generate text for sp_help, producing line numbers:

sp_helptext sp_help, NULL,NULL,'showsql,linenumbers'

Example 7

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'

Example 8

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)

Example 9

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"
-----------------

Example 10

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'

Usage

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

See also

System procedures sp_checksource, sp_configure, sp_hidetext