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.

NoteViews, 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.


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

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
create procedure myproc; as select 1
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
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

WHERE name = 'sp_droptabledef'
AND type = 'P'
DROP PROCEDURE sp_droptabledef
**Omni only
create procedure sp_droptabledef
  @tablename varchar(92) /*tablename*/
as begin
  declare @status int
  exec @status = sp_dropobjectdef @tablename
(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"
    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'



Any user can execute sp_helptext.


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


Audit option

Command or access audited

Information in extrainfo



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