DatabaseProcApplicationCreatedLinks
sybsystemprocssp_showtext_output  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** **************************************************************************
4     ** sp_showtext_output()
5     **
6     ** 	Work-horse procedure to generate the output from the rows collected in
7     ** 	a #temp table. This is where all the grunt work happens. Read the
8     **	rows from the #temp table, and parse it looking for newlines. Keep a
9     **	count of the # of lines processed. Fold-in support for various print
10    **	options while processing each line. If the object is a procedure with
11    **	groups, process and spit out SQL for each group, resetting line#s at
12    **	the start of each group.
13    **
14    ** Parameters:
15    **	@objname	- Name of object to generate DDL/SQL text for.
16    **	@objtype	- Type of object (used to generate DROP DDL).
17    **	@startline	- Starting line # from which to generate text.
18    **	@numlines_or_ctxt
19    **			- # of lines to print, from @startline. If the print
20    **			  option says, "context", treat this # as a context
21    **			  block around @startline.
22    **	@printopts	- Various print options decoded here.
23    **	@trace		- Debugging trace flags.
24    **	@art_terminator	- Artificial terminator byte added by caller.
25    **
26    ** Print options:
27    ** ==============
28    **	Comma-separated strings, each specifying a print style. In brief:
29    **
30    **	linenumbers	: Print line #s.
31    **	comments	: Print line #s in comments.
32    **	leftjustify	: Print line #s left-justified.
33    **	rightjustify	: Print line #s right-justified.
34    **	context		: Treat  param as a context block, and
35    **			  only print a context block starting from 
36    ** Tracing:
37    ** =======
38    **	1	: Print each row as you find it in syscomments.text.
39    **	2	: Print each row from syscomments w/"'"-delimited text.
40    **	3	: Print each sub-text as we parse past newlines.
41    **	4	: Trace iterations while searching for multiple newlines.
42    **	99	: Hang in print-line sproc to debug sp_procstack.
43    **	-ve #	: Trace execution of this sproc while lines around that
44    **		  line# are being processed.
45    {
46    */
47    create or replace procedure sp_showtext_output(
48        @objname varchar(317)
49        , @objtype varchar(2)
50        , @startline int = 0
51        , @numlines_or_ctxt int = 0
52        , @printopts varchar(256)
53        , @trace int = 0
54        , @art_terminator char(1)
55    ) as
56        begin
57            declare @linenum int
58                , @dbname varchar(30)
59                , @dropcmd varchar(256)
60                , @newline varchar(1)
61                , @tabchar varchar(1)
62                , @print_lnumbers tinyint -- whether to print line #
63                , @right_just tinyint -- Justification of line# field
64                , @print_lcomment tinyint -- line # in comments.
65                , @grep_ctxt tinyint -- only print context block
66                , @gen_ddl tinyint -- Generate DDL for creation.
67                , @newline_loc int
68                , @comma_loc int
69                , @grouping_num int
70                , @curr_group int
71                , @datalength int -- expected length of text
72                , @datalength_act int -- actual length of text
73                , @charlength_st int -- of sub-text column.
74                , @colid2 int
75                , @colid int
76                , @do_print int
77                , @text_is_hidden tinyint
78                , @proc_id int
79                , @charlength int
80    
81                , @text varchar(257)
82                -- The maximum length required 
83                -- for the text variable is
84                -- 255 (total no of bytes from
85                --      text column of 
86                --      syscomments)
87                -- + 1 (art_terminator byte)
88                -- + 1 (new_line character 
89                --      added in case of 
90                --      multiple partition 
91                --      condition rows)
92                -- = 257
93                , @trace_newline_fmtstr1 varchar(80)
94                , @trace_newline_fmtstr2 varchar(80)
95                , @multiple_groups tinyint -- To identify, in case of 
96                -- partition conditition, 
97                -- whether a single row of it
98                -- or the entire condition 
99                -- needs to be printed.
100   
101               -- We need longer buffers so that we can store repeated chunks
102               -- of 255 bytes that might appear in text column, w/o any
103               -- interleaving newline chars. For a table w/large # of columns,
104               -- and each column being very large, the list of columns that
105               -- 'SELECT *' expands can run into a long string, without any
106               -- interleaving newlines.
107               --
108               , @subtext varchar(1024)
109   
110               , @subtext_size int -- 1023
111               , @trace_linenum int -- line # to trace in particular
112               , @trace_linenum_ctxt tinyint -- context block around which
113               -- to trace processing when
114               -- doing particular line#
115               -- tracing.
116   
117               -- Supported print options
118               , @opt_sep char(1) -- option separator
119               , @sep_index int -- in @printopts
120               , @opt_showsql varchar(7)
121               , @opt_linenumbers varchar(12)
122               , @opt_comments varchar(10)
123               , @opt_rightjustify varchar(12)
124               , @opt_leftjustify varchar(12)
125               , @opt_ddlgen varchar(6)
126               , @opt_context varchar(7)
127               , @opt_noparams varchar(8)
128               , @opt_ctxt_sep char(1)
129               , @opt_ctxt_num_pat varchar(6)
130               , @option_item varchar(16) -- single option item
131   
132               -- Context block min/max values.
133               , @context_block int -- value of ctxt block
134               , @min_ctxt_linenum int
135               , @max_ctxt_linenum int
136               , @default_numlines tinyint
137               , @noparams tinyint
138   
139               -- Tracing levels.
140               , @trace_row tinyint
141               , @trace_row_text tinyint -- unimplemented
142               , @trace_subtext tinyint
143               , @trace_iterations tinyint
144               , @trace_hang_for_debug tinyint
145               , @retval int
146               , @option_found tinyint
147   
148           select @retval = 0
149   
150           -- Avoid noise errors; validate input data.
151           if (@startline < 0)
152           begin
153               select @text = "'@startline'=" + convert(varchar, @startline)
154               raiserror 19060, "sp_showtext", @text
155               select @retval = 1
156           end
157   
158           if (@numlines_or_ctxt < 0)
159           begin
160               select @text = "'@numlines_or_ctxt'=" + convert(varchar, @numlines_or_ctxt)
161               raiserror 19060, "sp_showtext", @text
162               select @retval = 1
163           end
164   
165           if (@retval != 0)
166               return @retval
167   
168           set nocount on
169   
170           select @linenum = 0 -- ASE counts from 0
171               , @trace_linenum = 0
172               , @trace_linenum_ctxt = 2
173               , @subtext = NULL
174               , @newline = char(10)
175               , @tabchar = char(9)
176               , @trace_row = 1
177               , @trace_row_text = 2 -- unimplemented
178               , @trace_subtext = 3
179               , @trace_iterations = 4
180               , @trace_hang_for_debug = 99
181               , @text_is_hidden = 0 -- assume text is not hidden
182               , @min_ctxt_linenum = 0
183               , @max_ctxt_linenum = 0
184               , @default_numlines = 5
185               , @noparams = 0
186   
187               -- Initialize print fmt strings for typical trace outputs.
188               , @trace_newline_fmtstr1 = ">>> trace: (Curr line#=%1!) Found a newline at location %2!. text=[%3!]"
189               , @trace_newline_fmtstr2 = ">>> trace: subtext=[%1!]"
190   
191               -- Initialize string constants for print options (documented)
192               --
193               , @opt_sep = ","
194               , @opt_showsql = "showsql"
195               , @opt_linenumbers = "linenumbers"
196               , @opt_comments = "comments"
197               , @opt_rightjustify = "rightjustify"
198               , @opt_leftjustify = "leftjustify"
199               , @opt_ddlgen = "ddlgen"
200               , @opt_context = "context"
201               , @opt_noparams = "noparams"
202   
203   
204           -- ###############################################################
205           -- Remember whether we are working with procedure groups.
206           -- Setup some default print options, and values
207           --
208           select @curr_group = 0
209               , @multiple_groups = 0
210               , @print_lnumbers = 0
211               , @right_just = 1 -- default
212               , @print_lcomment = 0
213               , @grep_ctxt = 0
214               , @context_block = 5 -- default value
215               , @gen_ddl = 0
216               , @subtext_size = 1023 -- max size of PRINT command
217               , @dbname = db_name()
218   
219           -- ###############################################################
220           -- Extract out and identify each print option requested.
221           --
222           if (@printopts = 'help')
223           begin
224               exec @retval = sp_showtext_usage @context_block
225                   , @opt_showsql
226                   , @opt_linenumbers
227                   , @opt_comments
228                   , @opt_leftjustify
229                   , @opt_rightjustify
230                   , @opt_ddlgen
231                   , @opt_context
232                   , @opt_noparams
233               return @retval
234           end
235           else if (@printopts IS NOT NULL)
236           begin
237               while (1 = 1)
238               begin -- {
239   
240                   exec @option_found = sp_getopts @printopts
241                       , @opt_sep
242                       , @option_item output
243                       , @printopts output
244   
245                   -- Terminate loop when we found all options.
246                   if (@option_found = 0)
247                       break
248   
249                   -- Support lower and upper-case, and mixed-case.
250                   select @option_item = lower(@option_item)
251   
252                   -- Decode option specified and set local variable.
253                   --
254                   if (@option_item = @opt_linenumbers)
255                   begin
256                       select @print_lnumbers = 1
257   
258                       -- If we are already asked to print in DDL
259                       -- style, then line #s will only be printed in
260                       -- comments, so that the output can be used to
261                       -- create the DDL.
262                       if (@gen_ddl = 1)
263                           select @print_lcomment = 1
264                   end
265   
266                   -- Option 'comments' => 'linenumbers'
267                   else if (@option_item = @opt_comments)
268                       select @print_lnumbers = 1, @print_lcomment = 1
269   
270                   else if (@option_item = @opt_rightjustify)
271                       select @right_just = 1
272   
273                   else if (@option_item = @opt_leftjustify)
274                       select @right_just = 0
275                   else if (@option_item = @opt_ddlgen)
276                   begin
277                       select @gen_ddl = 1
278   
279                       -- This option is not supported for the object
280                       -- type 'partition condition'
281                       if (@objtype = "N")
282                       begin
283                           exec sp_getmessage 17587, @text output
284                           select @text = @text + " (" + @objname + ")"
285                           raiserror 19859, 'showsql, ddlgen', @text
286                           return (1)
287                       end
288                       -- If we are also asked to print line#s, then
289                       -- always print line#s in comments, so that the
290                       -- resulting DDL is usable as a script.
291                       --
292                       if (@print_lnumbers = 1)
293                           select @print_lcomment = 1
294   
295                       -- Suppress it so that we don't get any
296                       -- messages from isql, in the output script,
297                       -- that needs to be run as a stand-alone DDL
298                       -- script.
299                       --
300                       set proc_return_status OFF
301                   end
302   
303                   else if (@option_item = @opt_context)
304                   begin
305                       -- This option is not supported for the object
306                       -- type 'partition condition'
307                       if (@objtype = "N")
308                       begin
309                           exec sp_getmessage 17587, @text output
310                           select @text = @text + " (" + @objname + ")"
311                           raiserror 19859, 'showsql, context', @text
312                           return (1)
313                       end
314   
315                       -- Once the user has specified 'context' in
316                       -- the print options, we *are* going to give 
317                       -- some context block. Use the user given
318                       -- context block value if it's non-zero.
319                       -- If it is 0, use a default value.
320                       --
321                       select @context_block =
322                           case
323                               when (@numlines_or_ctxt > 0)
324                               then @numlines_or_ctxt
325                               else @default_numlines
326                           end
327                           -- , @print_lnumbers = 1
328                           , @grep_ctxt = 1
329                   end
330   
331                   else if (@option_item = @opt_noparams)
332                   begin
333                       select @noparams = 1
334                   end
335                   else if (@option_item != @opt_showsql)
336                   begin
337                       -- Build the list of valid option.
338                       select @text = @opt_showsql
339                           + @opt_sep
340                           + @opt_linenumbers
341                           + @opt_sep
342                           + @opt_comments
343                           + @opt_sep
344                           + @opt_rightjustify
345                           + @opt_sep
346                           + @opt_leftjustify
347                           + @opt_sep
348                           + @opt_ddlgen
349                           + @opt_sep
350                           + @opt_context
351                           + @opt_sep
352                           + @opt_noparams
353   
354                       raiserror 18640, "'print'", @option_item
355                           , @text
356                       return 1
357                   end
358   
359               end -- }
360           end
361   
362           /*
363           ** See if the object is hidden (SYSCOM_TEXT_HIDDEN will be set)
364           */
365           if exists (select 1
366                   from syscomments
367                   where (status & 1 = 1)
368                       and id = object_id(@objname))
369           begin
370               select @text_is_hidden = 1
371           end
372   
373           -- ###############################################################
374           -- Validate options; check for mutually exclusive ones.
375           --
376           if (@grep_ctxt = 1) and (@gen_ddl = 1)
377           begin
378               -- Inject comment marker in 1st arg of this info msg.
379               exec sp_getmessage 19639, @text output
380               print @text, '--', @opt_context, @opt_ddlgen, @opt_context
381   
382               -- Reset local vars relating to context-block generation.
383               select @grep_ctxt = 0
384                   , @context_block = 0
385                   , @numlines_or_ctxt = 0
386           end
387   
388           -- ###############################################################
389           -- Start generating DDL if requested.
390   
391           if (@gen_ddl = 1)
392           begin
393               -- Generate 'use ' with blank line after 'go'
394               print "use %1!%2!go%3!%4!"
395                   , @dbname, @newline, @newline, @newline
396   
397               select @text = user_name()
398               print "IF EXISTS (SELECT 1 FROM sysobjects"
399               print "           WHERE name = '%1!'", @objname
400               print "             AND id = object_id('%1!')", @objname
401               print "             AND type = '%1!')", @objtype
402   
403               -- We don't have to deal with compiled objects of type RI
404               -- as these don't have rows in syscomments.
405               --
406               select @dropcmd = @tabchar
407                   + "DROP "
408                   + case @objtype
409                       when "P" then 'PROCEDURE %1!'
410                       when "TR" then 'TRIGGER %1!'
411                       when "V" then 'VIEW %1!'
412                       when "R" then 'RULE %1!'
413                       when "D" then 'DEFAULT %1!'
414                       when "F" then 'FUNCTION %1!'
415                       else "print '-- Unknown object type: '"
416                           + @objtype
417                   end
418               print @dropcmd, @objname
419               print "go"
420   
421               if (@text_is_hidden = 1)
422               begin
423                   select @proc_id = object_id(@objname)
424                   raiserror 18406, @objname, @proc_id
425                   return (1)
426               end
427           end
428   
429           -- ###############################################################
430           -- If we are asked to generate a context block of SQL text, help
431           -- the user by printing the sproc's name, and extract out some
432           -- arguments from syscolumns. This will give a bit more context to
433           -- the context block.
434           --
435           if (@grep_ctxt = 1)
436           begin
437               if (@noparams = 0)
438               begin
439                   exec @retval = sp_showtext_sproc_tagline
440                       @objname
441                       , @objtype
442                   if (@retval != 0)
443                       return @retval
444               end
445   
446               -- If text is hidden, we can't do much else trying to get a
447               -- context block. Bail out after generating the parameter
448               -- info. DON'T raise an error here, but just print a message,
449               -- so that if this proc is called from other procs (that are
450               -- drilling-down to the sproc's text using data from other
451               -- sources), the caller procs don't get a needless error.
452               -- Getting an error from here might affect the execution
453               -- logic of the caller proc.
454               --
455               if (@text_is_hidden = 1)
456               begin
457                   exec sp_getmessage 18406, @text output
458                   select @proc_id = object_id(@objname)
459                   print @text, @objname, @proc_id
460                   return 0
461               end
462           end
463   
464           -- Now that the only thing left is to extract the text, raise an
465           -- error if the text is hidden. There is nothing we can do here.
466           if (@text_is_hidden = 1)
467           begin
468               select @proc_id = object_id(@objname)
469               raiserror 18406, @objname, @proc_id
470               return 1
471           end
472   
473           -- Setup min/max context block line numbers, depending on whether
474           -- we are searching for a context block, or simply serching for
475           -- a given # of lines.
476           --
477           if (@grep_ctxt = 1)
478           begin
479               select @min_ctxt_linenum = (@startline - @context_block)
480                   , @max_ctxt_linenum = (@startline + @context_block)
481           end
482           else if (@numlines_or_ctxt > 0)
483           begin
484               -- Leave this as-is, and don't try to do "- 1 " for loop
485               -- termination. In some cases, we still have to process
486               -- after we do "goto end_cursor" whatever is the remaining
487               -- SQL fragment of the 'last' line that gets  printed
488               -- inside this loop.
489               --
490               select @max_ctxt_linenum = (@startline + @numlines_or_ctxt)
491           end
492   
493           -- ###############################################################
494           if (@trace = @trace_row)
495           begin
496               print ">>> trace print_lnumbers: %1! right_just: %2! print_lcomment: %3! gen_ddl: %4! trace: %5! startline=%6! min_ctxt_linenum=%7! max_ctxt_linenum=%8! grep_ctxt=%9!"
497                   , @print_lnumbers, @right_just, @print_lcomment, @gen_ddl
498                   , @trace
499                   , @startline
500                   , @min_ctxt_linenum
501                   , @max_ctxt_linenum
502                   , @grep_ctxt
503           end
504   
505           -- ###############################################################
506           -- Process each text row from syscomments.
507           -- ###############################################################
508   
509           declare curline cursor for
510           select number, text, charlength_text, colid2, colid
511           from #syscomments
512           order by line
513   
514           open curline
515   
516           while (1 = 1)
517           begin -- {
518               fetch curline into @grouping_num
519                   , @text
520                   , @datalength
521                   , @colid2
522                   , @colid
523               if (@@sqlstatus != 0)
524                   break
525   
526               -- bail out early if we are already past the # of lines
527               -- needed to be processed.
528               --
529               if (((@grep_ctxt = 1) or (@numlines_or_ctxt > 0))
530                       and (@linenum >= @max_ctxt_linenum))
531               begin
532                   goto end_cursor
533               end
534   
535               select @datalength_act = datalength(@text)
536   
537               -- Print a leader line if procedure group changes or
538               -- in case of object type being partition condition,
539               -- print a newline character to mark the change of
540               -- the partition condition row
541               if (@curr_group != @grouping_num)
542               begin
543                   if (@gen_ddl = 1) and (@grouping_num >= 2)
544                       print "go%1!", @newline
545   
546                   if (@objtype = "N") and (@grouping_num >= 2)
547                   begin
548                       -- Only in case of multiple partition condition
549                       -- rows to be printed, add a newline at the
550                       -- beginning of the row. Otherwise, keep it as 
551                       -- it is to avoid extra leading empty line.
552                       if (@multiple_groups = 1)
553                           select @text = @newline + @text
554                   end
555   
556                   select @curr_group = @grouping_num
557                       , @multiple_groups = 1
558   
559                       -- In case of partition condition object, see
560                       -- that the linenumber starts with 1 and 
561                       -- continues to be the same value provided by 
562                       -- the previous loop(as the increment by 1 for 
563                       -- every encounter of newline is taken care of 
564                       -- in the previous loop).However, in case of 
565                       -- procedure object, linenumber is set back to 
566                       -- 1 as the procedure group changes.
567   
568                       , @linenum = case @objtype
569                           when "N"
570                           then case @linenum
571                               when 0 then 1
572                               else @linenum
573                           end
574                           else 1
575                       end
576               end
577   
578               if (@trace = @trace_subtext)
579               begin
580                   print ">>> trace: (line#=%1! colid2=%2! colid=%3!) Found a new row of length=%4!, actual length=%5! text: [%6!]"
581                       , @linenum, @colid2, @colid
582                       , @datalength, @datalength_act, @text
583               end
584   
585               -- Find if this text line has a newline char in it.
586               select @newline_loc = charindex(@newline, @text)
587   
588               -- Passing in a -ve trace level means user
589               -- is interested in tracing the processing of
590               -- SQL around that line number. Convert the
591               -- trace to a real line number.
592               --
593               if (@trace < 0)
594               begin
595                   select @trace_linenum = abs(@trace)
596   
597                   if (@trace_linenum between
598                           (@linenum - @trace_linenum_ctxt)
599                           and (@linenum + @trace_linenum_ctxt))
600                   begin
601                       print @trace_newline_fmtstr1
602                           , @linenum, @newline_loc, @text
603                       print @trace_newline_fmtstr2
604                           , @subtext
605                   end
606               end
607   
608               -- While there are newline chars embedded, output ind. lines.
609               --
610               while (@newline_loc != 0)
611               begin -- {
612                   if (@trace < 0)
613                   begin
614                       -- Passing in a -ve trace level means user
615                       -- is interested in tracing the processing of
616                       -- SQL around that line number. Convert the
617                       -- trace to a real line number.
618                       --
619                       select @trace_linenum = abs(@trace)
620   
621                       if (@trace_linenum between
622                               (@linenum - @trace_linenum_ctxt)
623                               and (@linenum + @trace_linenum_ctxt))
624                       begin
625                           print @trace_newline_fmtstr1
626                               , @linenum, @newline_loc, @text
627                           print @trace_newline_fmtstr2
628                               , @subtext
629                       end
630                   end
631   
632                   else if (@trace = @trace_iterations)
633                   begin
634                       print @trace_newline_fmtstr1
635                           , @linenum, @newline_loc, @text
636                       print @trace_newline_fmtstr2
637                           , @subtext
638                   end
639   
640                   -- Extract out the prefix portion of the line.
641                   -- If there is a blank line in the code, we will
642                   -- get it here as a 1st char in the newline. In
643                   -- that case, pass that one through, so that we
644                   -- print blank lines also.
645                   --
646                   select @subtext = substring(@subtext, 1,
647                           (char_length(@subtext) - 1))
648                       + case @newline_loc
649                           when 1 then @newline
650                           else substring(@text, 1,
651                                   @newline_loc - 1)
652                       end
653   
654                   -- Skip this logic when applying it to this *very*
655                   -- sproc. Otherwise, we will end up incorrectly
656                   -- decrementing line#s for this sproc, whereas the
657                   -- embedded string is really in code, and not generated
658                   -- by SELECT * expansion.
659                   --
660                   if ((@subtext LIKE
661                               "%Adaptive Server has expanded all%")
662                           and (@grep_ctxt = 1)
663                           and (@objname != 'sp_showtext_output'))
664                   begin
665                       -- In 12.5.x, we didn't use to leave any 
666                       -- newlines before and after the generated
667                       -- message. In 15.0 onwards, we leave two
668                       -- blank lines. Account for that in this 
669                       -- line# book-keeping logic.
670                       --
671                       select @linenum = @linenum
672                           - (case @@version_as_integer
673                               when 12500 then 0
674                               else 2
675                           end)
676                   end
677   
678                   -- Generate line number as a 5-char wide string,
679                   -- right justified.
680                   --
681                   exec @do_print = sp_showtext_check_print
682                       @startline
683                       , @numlines_or_ctxt
684                       , @grep_ctxt
685                       , @min_ctxt_linenum
686                       , @max_ctxt_linenum
687                       , @linenum
688                       , @gen_ddl
689                   if (@do_print != 0)
690                   begin
691                       exec sp_showtext_print_line
692                           @print_lnumbers
693                           , @right_just
694                           , @print_lcomment
695                           , @grep_ctxt
696                           , @linenum
697                           , @subtext
698                           , @do_print
699                           , @trace
700                           , @trace_hang_for_debug
701                   end
702   
703                   select @linenum = @linenum + 1
704   
705                   -- Strip out the leading part before newline
706                   -- that just got printed above. The remaining portion
707                   -- will be what we will process further looking for
708                   -- more newlines to break out.
709                   --
710                   select @text = stuff(@text, 1, @newline_loc, NULL)
711   
712                   select @newline_loc = charindex(@newline, @text)
713                       , @subtext = NULL
714               end -- }
715   
716               -- Retain the balance remaining to be concatenated with the 
717               -- start of the next line. When we saved off the text data
718               -- into a #temp table in the caller, an artificial terminator
719               -- byte was padded to avoid truncation of trailing blanks
720               -- when the FETCH would happen. This artificial terminator will
721               -- be skipped when re-generating the text back.
722               --
723               select @charlength = char_length(@text)
724   
725               if (@charlength = 1 and (@text = @newline))
726               begin
727                   print "%1!", @text
728               end
729               else if ((@charlength = 2)
730                       and (@text = @newline + @art_terminator))
731               begin
732                   select @text = substring(@text, 1, 1)
733                   print "%1!", @text
734               end
735               else if (@charlength != 0)
736               begin -- {
737                   -- We might have accumulated an internally generated
738                   -- terminator byte. Strip that out first before
739                   -- we concatenate the subtext from the previous
740                   -- iteration to the text.
741                   --
742                   -- If we are already full in the sub-text buffer
743                   -- that we've been accumulating, dump that out now,
744                   -- so that the user at least gets some data even
745                   -- though the newline might not be at at the right
746                   -- location. (This restriction arises from SQL PRINT
747                   -- command that has a limit of 1024 bytes.)
748                   --
749                   select @charlength = char_length(@subtext)
750   
751                   if (datalength(@subtext) + datalength(@text)
752                           > @subtext_size)
753                   begin -- {
754                       select @subtext = substring(@subtext, 1,
755                               (@charlength - 1))
756   
757                       -- Find the last ", " character in the string,
758                       -- which is most likely the separator for the
759                       -- list of columns in the expanded SELECT *
760                       -- output. Truncate the list there and print
761                       -- this piece on one line. As we are doing
762                       -- the charindex() search on the reverse of
763                       -- the string, search for " ,".
764                       --
765                       select @charlength_st = char_length(@subtext)
766   
767                       select @comma_loc = @charlength_st
768                           - (charindex(' ,',
769                               reverse(@subtext)))
770   
771                       -- Save trailing piece for now, so that we
772                       -- can restore it after printing header piece.
773                       -- (Reuse variable to save space.)
774                       --
775                       if (@comma_loc > 0)
776                       begin
777                           select @dropcmd = substring(@subtext,
778                                   (@comma_loc + 2),
779                                   (@charlength_st
780                                   - (@comma_loc + 1)))
781   
782                           select @subtext = substring(@subtext, 1,
783                                   @comma_loc)
784                       end
785   
786                       if ((@subtext LIKE
787                                   "%Adaptive Server has expanded all%")
788                               and (@grep_ctxt = 1)
789                               and (@objname != 'sp_showtext_output'))
790                       begin
791                           select @linenum = @linenum - 2
792                       end
793   
794                       -- Generate line number as a 5-char wide string,
795                       -- right justified.
796                       --
797                       -- Do *NOT* increment the line#s here, as all
798                       -- we are doing is dumping out stuff that
799                       -- we don't have room for in our buffers. This
800                       -- text is (probably) internally generated
801                       -- by SELECT * expansion and there are no new
802                       -- lines in this generated text.
803                       --
804                       exec @do_print = sp_showtext_check_print
805                           @startline
806                           , @numlines_or_ctxt
807                           , @grep_ctxt
808                           , @min_ctxt_linenum
809                           , @max_ctxt_linenum
810                           , @linenum
811                           , @gen_ddl
812                       if (@do_print != 0)
813                       begin
814                           exec sp_showtext_print_line
815                               @print_lnumbers
816                               , @right_just
817                               , @print_lcomment
818                               , @grep_ctxt
819                               , @linenum
820                               , @subtext
821                               , @do_print
822                               , @trace
823                               , @trace_hang_for_debug
824                       end
825   
826                       /*
827                       ** Don't increment any line# variable here
828                       ** as this is for a lapsed case where the
829                       ** text in syscomments is essentially one
830                       ** long line, but PRINT can't print it in
831                       ** one piece. So we print it in sub-chunks.
832                       */
833   
834                       -- Restore trailing piece that was saved off,
835                       -- if any. @subtext will be retained for the
836                       -- next iteration, and is known to not
837                       -- contain any more newlines. This piece
838                       -- will be prependended to the next text
839                       -- fragment found.
840                       --
841                       select @subtext = case @comma_loc
842                               when 0 then NULL
843                               else @dropcmd
844                           end
845                           + @text
846   
847                   end -- }
848                   else
849                   begin
850                       -- @subtext was retained as the trailing text
851                       -- fragment from the previous row. It would
852                       -- have contained the artificial terminator
853                       -- char. Strip that off first, before appending
854                       -- the currently found text fragment to the
855                       -- trailing fragment from the previous row.
856                       --
857                       select @subtext
858                           = substring(@subtext, 1,
859                               (@charlength - 1))
860                           + @text
861   
862                       if (@trace = @trace_subtext)
863                       begin
864                           print ">>> trace: subtext:[%1!]", @subtext
865                       end
866                   end
867               end -- }
868               else
869               begin
870                   select @subtext = NULL
871               end
872   
873           end -- }
874   
875   end_cursor:
876           close curline
877   
878           deallocate cursor curline
879   
880           -- ###############################################################
881           -- There still might be a last piece of text left over after the
882           -- cursor has finished. This happens when non-isql applns create
883           -- a procedure (e.g. through CT-lib appln) and do not provide a
884           -- newline terminator for the last line. Print that out.
885           --
886   
887           exec @do_print = sp_showtext_check_print
888               @startline
889               , @numlines_or_ctxt
890               , @grep_ctxt
891               , @min_ctxt_linenum
892               , @max_ctxt_linenum
893               , @linenum
894               , @gen_ddl
895           if ((@subtext != "," and char_length(@subtext) > 1)
896                   and (@do_print != 0))
897           begin
898               select @subtext = substring(@subtext, 1,
899                       (char_length(@subtext) - 1))
900               if ((@subtext LIKE
901                           "%Adaptive Server has expanded all%")
902                       and (@grep_ctxt = 1)
903                       and (@objname != 'sp_showtext_output'))
904               begin
905                   select @linenum = @linenum - 2
906               end
907   
908               exec sp_showtext_print_line
909                   @print_lnumbers
910                   , @right_just
911                   , @print_lcomment
912                   , @grep_ctxt
913                   , @linenum
914                   , @subtext
915                   , @do_print
916                   , @trace
917                   , @trace_hang_for_debug
918           end
919   
920           -- Just one \n after go is enough. Don't inject what user's scripts
921           -- might now already have.
922           --
923           if (@gen_ddl = 1)
924           begin
925               print "go%1!", @newline
926           end
927       end
928   


exec sp_procxmode 'sp_showtext_output', 'AnyMode'
go

Grant Execute on sp_showtext_output to public
go
DEFECTS
 MTYP 4 Assignment type mismatch @opt_linenumbers: varchar(11) = varchar(12) 226
 MTYP 4 Assignment type mismatch @opt_comments: varchar(8) = varchar(10) 227
 MTYP 4 Assignment type mismatch @opt_leftjustify: varchar(11) = varchar(12) 228
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause curline 510
 MAW1 3 Warning message on %name% sybsystemprocs..syscomments.id: Warning message on syscomments 368
 MGTP 3 Grant to public sybsystemprocs..sp_showtext_output  
 MGTP 3 Grant to public sybsystemprocs..syscomments  
 MNER 3 No Error Check should check return value of exec 224
 MNER 3 No Error Check should check return value of exec 283
 MNER 3 No Error Check should check return value of exec 309
 MNER 3 No Error Check should check return value of exec 379
 MNER 3 No Error Check should check return value of exec 457
 MNER 3 No Error Check should check return value of exec 691
 MNER 3 No Error Check should check return value of exec 814
 MNER 3 No Error Check should check return value of exec 908
 MUCO 3 Useless Code Useless Brackets in create proc 47
 MUCO 3 Useless Code Useless Begin-End Pair 56
 MUCO 3 Useless Code Useless Brackets 151
 MUCO 3 Useless Code Useless Brackets 158
 MUCO 3 Useless Code Useless Brackets 165
 MUCO 3 Useless Code Useless Brackets 222
 MUCO 3 Useless Code Useless Brackets 235
 MUCO 3 Useless Code Useless Brackets 237
 MUCO 3 Useless Code Useless Brackets 246
 MUCO 3 Useless Code Useless Brackets 254
 MUCO 3 Useless Code Useless Brackets 262
 MUCO 3 Useless Code Useless Brackets 267
 MUCO 3 Useless Code Useless Brackets 270
 MUCO 3 Useless Code Useless Brackets 273
 MUCO 3 Useless Code Useless Brackets 275
 MUCO 3 Useless Code Useless Brackets 281
 MUCO 3 Useless Code Useless Brackets 286
 MUCO 3 Useless Code Useless Brackets 292
 MUCO 3 Useless Code Useless Brackets 303
 MUCO 3 Useless Code Useless Brackets 307
 MUCO 3 Useless Code Useless Brackets 312
 MUCO 3 Useless Code Useless Brackets 323
 MUCO 3 Useless Code Useless Brackets 331
 MUCO 3 Useless Code Useless Brackets 335
 MUCO 3 Useless Code Useless Brackets 391
 MUCO 3 Useless Code Useless Brackets 421
 MUCO 3 Useless Code Useless Brackets 425
 MUCO 3 Useless Code Useless Brackets 435
 MUCO 3 Useless Code Useless Brackets 437
 MUCO 3 Useless Code Useless Brackets 442
 MUCO 3 Useless Code Useless Brackets 455
 MUCO 3 Useless Code Useless Brackets 466
 MUCO 3 Useless Code Useless Brackets 477
 MUCO 3 Useless Code Useless Brackets 482
 MUCO 3 Useless Code Useless Brackets 494
 MUCO 3 Useless Code Useless Brackets 516
 MUCO 3 Useless Code Useless Brackets 523
 MUCO 3 Useless Code Useless Brackets 529
 MUCO 3 Useless Code Useless Brackets 541
 MUCO 3 Useless Code Useless Brackets 552
 MUCO 3 Useless Code Useless Brackets 578
 MUCO 3 Useless Code Useless Brackets 593
 MUCO 3 Useless Code Useless Brackets 597
 MUCO 3 Useless Code Useless Brackets 610
 MUCO 3 Useless Code Useless Brackets 612
 MUCO 3 Useless Code Useless Brackets 621
 MUCO 3 Useless Code Useless Brackets 632
 MUCO 3 Useless Code Useless Brackets 660
 MUCO 3 Useless Code Useless Brackets 672
 MUCO 3 Useless Code Useless Brackets 689
 MUCO 3 Useless Code Useless Brackets 725
 MUCO 3 Useless Code Useless Brackets 729
 MUCO 3 Useless Code Useless Brackets 735
 MUCO 3 Useless Code Useless Brackets 751
 MUCO 3 Useless Code Useless Brackets 768
 MUCO 3 Useless Code Useless Brackets 775
 MUCO 3 Useless Code Useless Brackets 786
 MUCO 3 Useless Code Useless Brackets 812
 MUCO 3 Useless Code Useless Brackets 862
 MUCO 3 Useless Code Useless Brackets 895
 MUCO 3 Useless Code Useless Brackets 900
 MUCO 3 Useless Code Useless Brackets 923
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered
(id, number, colid2, colid, texttype)
Intersection: {id}
367
 VNRD 3 Variable is not read @trace_row_text 177
 VUNU 3 Variable is not used @sep_index 119
 VUNU 3 Variable is not used @opt_ctxt_sep 128
 VUNU 3 Variable is not used @opt_ctxt_num_pat 129
 CRDO 2 Read Only Cursor Marker (has an 'order by' clause) 510
 MSUB 2 Subquery Marker 365
 MTR1 2 Metrics: Comments Ratio Comments: 41% 47
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 70 = 77dec - 9exi + 2 47
 MTR3 2 Metrics: Query Complexity Complexity: 254 47

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_showtext_print_line  
calls proc sybsystemprocs..sp_getmessage  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
   reads table sybsystemprocs..sysusermessages  
calls proc sybsystemprocs..sp_showtext_usage  
   calls proc sybsystemprocs..sp_getmessage  
reads table tempdb..#syscomments (1) 
calls proc sybsystemprocs..sp_getopts  
calls proc sybsystemprocs..sp_showtext_check_print  
calls proc sybsystemprocs..sp_showtext_sproc_tagline  
   calls proc sybsystemprocs..sp_help_params  
      read_writes table tempdb..#helpproc (1) 
      reads table sybsystemprocs..systypes  
      calls proc sybsystemprocs..sp_autoformat  
         writes table sybsystemprocs..sp_autoformat_rset_005 
         writes table sybsystemprocs..sp_autoformat_rset_002 
         calls proc sybsystemprocs..sp_namecrack  
         reads table master..syscolumns (1)  
         writes table sybsystemprocs..sp_autoformat_rset_004 
         writes table sybsystemprocs..sp_autoformat_rset_001 
         read_writes table tempdb..#colinfo_af (1) 
         reads table tempdb..systypes (1)  
         reads table master..systypes (1)  
         calls proc sybsystemprocs..sp_autoformat  
         reads table tempdb..syscolumns (1)  
         writes table sybsystemprocs..sp_autoformat_rset_003 
      reads table master..spt_values (1)  
      reads table sybsystemprocs..sysxtypes  
      reads table master..sysmessages (1)  
      reads table sybsystemprocs..syscolumns  
reads table sybsystemprocs..syscomments  

CALLERS
called by proc sybsystemprocs..sp_showtext  
   called by proc sybsystemprocs..sp_helptext_usage  
      called by proc sybsystemprocs..sp_helptext  
   called by proc sybsystemprocs..sp_helptext