sp_helptext

sp_helptext now reports the text of user-defined functions.

sp_helptext also has some new parameters, numlines and printops:

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

Specifies the numbers of lines for which to generate SQL text. The meaning of this argument can change, depending on the output properties you specify in .If printops includes showsql, numlines is treated as the number of lines of SQL text to display. If printops also includes context, numlines is the context block width that surrounds the starting line number. In that case, the SQL text generated is the output for the stated number of lines of context blocked around the requested starting line number.

Supports comma-separated properties of the output format, selected by the user. You can specify one or more of these print options, in any order, as a comma-separated string:

• showsql – formatted SQL output for the compiled object. This argument passes control to sp_showtext; if showsql does not appear in the printopts list, the new functionality is not invoked.

• linenumbers – produces line numbers for each line of SQL output. There are no line numbers produced by default.

• comments – produces the line numbers as a comment field (*/; <nnn>/*), so that the SQL generated is usable for recreating the compiled object, if necessary, without further edits. No comment block is produced by default.

• context – produces a context block of output around the specified starting line numbers. If you supply no, or null, numlines parameter, a default context block of five lines is supplied. To get line numbers when displaying a context block of SQL, use context, linenumbers.

• 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 DDL script, prefacing the output with a use <database> command, and a drop <object> command.