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 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 , @space_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 @space_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 (@space_loc > 0)
776 begin
777 select @dropcmd = substring(@subtext,
778 @space_loc,
779 (@charlength_st
780 - @space_loc))
781
782 select @subtext = substring(@subtext, 1,
783 (@space_loc - 1))
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 @space_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
