New parameters for sp_helptext.
sp_helptext [, "objname" , linenum, numlines , "printoptions" ]
linenum – specifies the starting line number from which the SQL text is generated.
numlines – specifies the number of lines for which to generate SQL text. If printoptions includes the term showsql, numlines specifies the number of lines of SQL text to display. If printoptions also includes the term context, numlines specifies the width of the context block surrounding linenum.
printoptions – a comma-separated list of terms specifying one or more properties for the output format, including:
showsql – generates formatted SQL output for the compiled object.
linenumbers – produces a line number for each line of SQL output.
comments – produces the line numbers as a comment field, so that the generated SQL can be used, with additional edits, to recreate the compiled object.
context – produces a context block of output around a specified starting line number. The width of this context block surrounding the line number is as specified by the parameter numlines. The default number of lines of context is 5.
noparams – suppresses the automatically generated parameter information; use this option to produce only the portion of SQL output relevant to a compiled object.
ddlgen – generates the SQL text as a minimal DDL script, prefacing output with use <database> and drop <object>.
ddlgen is for the use of sp_helptext only.
Do not confuse it with the Sybase utility DDLGEN.
Example 1 To generate the formatted text for sp_help:
sp_helptext sp_help, NULL, NULL, "showsql"
Example 2 To generate the formatted SQL text for sp_help with line numbers:
sp_helptext sp_help, NULL, NULL,"showsq l,linenumbers"
Example 3 To generate a context block of 7 lines, starting at line 25, with line numbers generated in a comment block:
sp_helptext sp_help, 25, 7, "showsql, linenumbers, comments, context"
The object whose text you want to retrieve must reside in the database where the procedure is executed.
If there is no text in syscomments, or if it is hidden by sp_hidetext, an error is reported, unless you request this context block output. In that case no error is raised, but a message reporting the missing text displays.
If the compiled object contains a SQL select *, this statement is usually expanded on creation to reflect the entire column list of the table to which the select clause refers.
SQL generated by ddlgen is minimal; for a more complete SQL script, use the Sybase ddlgen utility. The SQL generated by ddlgen may fail to create the compiled object if certain references to other objects, such as temporary tables, do not already exist when the generated script is executed.
Using ddlgen and context together raises an error.
