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

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

Adaptive Server displays the text for predicates. sp_helptext can be supplied the predicate’s user-defined name, if there is one, or its internal name. For example:

sp_helptext pred1

# Lines of Text
 ---------------
          1
text ----------------------------------------------------
 grant select on tab1 where col1 = 5 as pred1 to robert

Usage