Database	Proc	Application	Created	Links
sybsystemprocs	sp_helptext		31 Aug 14	Defects Dependencies

1     
2     /*
3     ** sp_helptext
4     **
5     **	Top-level procedure to generate text for a compiled object from
6     **	syscomments. This interface supports the standard interface to extract
7     **	255-byte chunks of text from syscomments, and also provides an
8     **	interface to call into the expanded text-reporting utility procedure,
9     **	sp_showtext. The call-out to that procedure is through the extended
10    **	@printopts argument.
11    **
12    ** Parameters:
13    **	@objname	- Name of compiled object to generate text for.
14    **	@grouping_num	- Sproc grouping number.
15    **
16    ** If the @printopts arg is used to specify the 'showsql' argument, then
17    ** the meaning of various parameters is as follows:
18    **
19    **	@grouping_num	- Starting line # from which to generate text.
20    **	@numlines	- Number of lines of text to generate.
21    **	@printopts	- Comma-separated string of qualifiers for the
22    **			  generated text.
23    **	@trace		- Trace facility; for internal use only.
24    **
25    ** The @objname arg can be used in one of two ways:
26    **
27    **	sp_helptext , 3
28    **	sp_helptext ";3", 5, 20, 'showsql'
29    **
30    ** In the first way, '3' is the procedure group number (for procedure
31    ** groups), or is the partition condition number when  is a partition
32    ** condition type.
33    **
34    ** In the second way, the group number or partition condition number '3' is
35    ** snuck away as part of the object name. Then, as we are running in 'showsql
36    ** mode, the next two args are (starting line#, # of lines).
37    **
38    ** See the help/usage info for more details on the interface with examples.
39    {
40    */
41    create procedure sp_helptext(
42        @objname varchar(325) = NULL
43        , @grouping_num int = NULL
44        , @numlines int = NULL
45        , @printopts varchar(256) = NULL
46        , @trace int = 0
47    ) as
48        begin
49            declare @text_count int
50            declare @name varchar(255)
51            declare @config_parm int
52            declare @issystemproc int
53            declare @procval int
54            declare @proc_id int
55            declare @type char(2)
56            declare @tot_members int
57                , @objname_orig varchar(325) -- What user entered.
58                , @group_num_str varchar(10)
59                , @opt_showsql varchar(7)
60                , @char_index int
61                , @retval int
62    
63            if (@objname IS NULL)
64            begin
65                exec sp_helptext_usage
66                return 0
67            end
68    
69            if @@trancount = 0
70            begin
71                set chained off
72            end
73    
74            set transaction isolation level 1
75    
76            select @objname_orig = @objname
77                , @opt_showsql = 'showsql'
78    
79            /* See if @objname has a group # reference. Strip it out first. */
80            select @char_index = charindex(';', @objname)
81            if (@char_index != 0)
82            begin
83                -- This is the piece of string following ';'
84                select @group_num_str = ltrim(rtrim(substring(@objname,
85                                (@char_index + 1),
86                                (datalength(@objname)
87                                - @char_index))))
88    
89                -- Retrieve just the, possibly multi-part, object name.
90                select @objname = substring(@objname, 1, (@char_index - 1))
91            end
92            /*
93            **  Make sure the @objname is local to the current database.
94            */
95            if @objname like "%.%.%" and
96                substring(@objname, 1, charindex(".", @objname) - 1) != db_name()
97            begin
98                /* 17460, "Object must be in the current database." */
99                raiserror 17460
100               return (1)
101           end
102   
103           /*
104           **  See if @objname exists.
105           */
106           if (object_id(@objname) is NULL)
107           begin
108               /* 17461, "Object does not exist in this database." */
109               raiserror 17461
110               return (1)
111           end
112   
113           /*
114           ** If the configuration parameter 'select on syscomments.text' is set to
115           ** 0, then  the user can use sp_helptext ONLY in the following cases
116           ** 
117           **	1. if the user has sa_role
118           **	2. if the user is dbo or aliased to dbo
119           **	3. if the object is a system stored procedure
120           **	4. if the object is owned by the user
121           **
122           ** For SMP or SDC, only one row is expected.
123           */
124           select @config_parm = value
125           from master.dbo.syscurconfigs
126           where config = 258
127   
128   
129           select @issystemproc = 1 /* It is a system stored procedure */
130   
131           if @config_parm = 0
132           begin
133               /*
134               ** If the user is DBO or an account with sa_role, it's all fine
135               */
136               if (user_id() != 1)
137               begin
138                   if (db_name() != "master" and db_name() != "sybsystemprocs")
139                   begin
140                       /*
141                       ** System Stored Procedures can only be in master or
142                       ** sybsystemsprocs database.
143                       */
144                       select @issystemproc = 0
145                   end
146                   else
147                   begin
148                       /*
149                       ** We are in either sybsystemprocs or master database.
150                       ** See if the name starts with "sp_" and is owned by
151                       ** the DBO.
152                       */
153                       select @name = name from sysobjects where
154                           id = object_id(@objname)
155                           and uid = 1
156                       if (substring(@name, 1, 3) != "sp_")
157                           select @issystemproc = 0
158                   end
159   
160                   /*
161                   ** If it is not a system stored procedure, then it needs to be
162                   ** owned by the user.
163                   */
164                   if @issystemproc = 0
165                   begin
166                       /*
167                       ** The object needs to be owned by the user
168                       */
169                       if not exists (select name from sysobjects
170                               where uid = user_id()
171                                   and id = object_id(@objname))
172                       begin
173                           raiserror 18180
174                           return (1)
175                       end
176                   end
177               end
178               else
179               begin
180                   /*
181                   ** Audit this as a successful sa command execution if the user
182                   ** has sa role.
183                   */
184                   if charindex("sa_role", show_role()) > 0
185                       select @procval = proc_role("sa_role")
186               end
187           end
188   
189           -- ============================================================================ 
190           -- Check to see if user is running in improved 'showsql' mode, where they
191           -- are interested in getting text formatted as it was input. If so, call
192           -- the new sub-proc, and return from here. (Otherwise, we end up calling
193           -- in legacy mode with pre-showsql semantics for the arguments.)
194           --
195           if (@printopts IS NOT NULL)
196           begin
197               if (@printopts LIKE "%" + @opt_showsql + "%")
198               begin
199                   exec @retval = sp_showtext @objname_orig, @grouping_num
200                       , @numlines, @printopts, @trace
201                   return @retval
202               end
203               else
204               begin
205                   -- A non-NULL @printopts *must* have  at least 'showsql',
206                   -- otherwise, it's meaningless.
207                   --
208                   raiserror 19384, @opt_showsql, "@printopts"
209                   return 1
210               end
211           end
212   
213   
214           /*  Validate grouping number */
215           select @type = type from sysobjects
216           where id = object_id(@objname)
217   
218           /*
219           ** The group number is for stored procedures, but is overloaded
220           ** for the partition order of the partition condition object.
221           ** Using @grouping_num n means to get the nth partition condition
222           ** text.
223           */
224           if (@type NOT IN ('P', 'N')) and (@grouping_num is not null)
225           begin
226               /*
227               ** You cannot use a grouping number for non-procedures.
228               */
229               raiserror 18936
230               return (1)
231           end
232   
233           -- Validate the case when both "table;",  interface is used, but the
234           -- user didn't say 'showsql'. That's an error. We only allow "sproc;"
235           -- notation when sp_showtext will be called.  It's ambiguous to decide
236           -- what grouping # user means when they have specified it both ways
237           -- via "proc;3" and the @grouping_num == 5. Error out.
238           --
239           if (@group_num_str IS NOT NULL)
240           begin
241               if (@grouping_num IS NOT NULL)
242               begin
243                   -- We will really never come here as the missing 'showsql'
244                   -- has been trapped earlier, already. Just in case, report
245                   -- a generic error.
246                   --
247                   raiserror 19384, @opt_showsql, @objname_orig
248                   return 1
249               end
250               else if (patindex("%[^0-9]%", @group_num_str) = 0)
251               begin
252                   -- Allow "proc;" notation to extract SQL for just one group.
253                   select @grouping_num = convert(int, @group_num_str)
254               end
255               else
256               begin
257                   -- Reuse variable to create 2nd arg to error message.
258                   select @objname_orig = "@grouping_num='"
259                       + @group_num_str
260                       + "'"
261                   raiserror 19060, 'sp_helptext', @objname_orig
262                   return 1
263               end
264           end
265   
266           if @grouping_num is null
267           begin
268               /*
269               ** Find out how many lines of text are coming back.
270               ** and return if there are none.
271               */
272               select @text_count = count(*)
273               from syscomments
274               where id = object_id(@objname)
275   
276               if @text_count = 0
277               begin
278                   /* 17679, "There is no text for object @objname." */
279                   raiserror 17679, @objname
280                   return (1)
281               end
282           end
283           else
284           begin
285               /*
286               ** Find out how many lines of text are coming back.
287               ** and return if there are none.
288               */
289               select @text_count = count(*)
290               from syscomments
291               where id = object_id(@objname) and
292                   number = @grouping_num
293   
294               if @text_count = 0
295               begin
296                   raiserror 18937, @objname, @grouping_num
297                   return (1)
298               end
299           end
300   
301           /*
302           ** See if the object is hidden (SYSCOM_TEXT_HIDDEN will be set)
303           */
304           if exists (select 1
305                   from syscomments where (status & 1 = 1)
306                       and id = object_id(@objname))
307           begin
308               /*
309               ** 18406, "Source text for compiled object %!1
310               ** (id = %!2) is hidden."
311               */
312               select @proc_id = object_id(@objname)
313               raiserror 18406, @objname, @proc_id
314               return (1)
315           end
316   
317           /*
318           **  Return # howmany lines of text that are about to come back.
319           **  This is required by the "old" report writer.
320           */
321           select "# Lines of Text" = @text_count
322   
323           /*
324           **  Now get the text.
325           */
326           if @grouping_num is null
327           begin
328               /* Are there multiple group members? */
329               select @tot_members = count(distinct number)
330               from syscomments
331               where id = object_id(@objname)
332   
333               if @tot_members > 1
334               begin
335                   /*
336                   ** Display text of all grouped procedures.
337                   ** Include grouping number in output.
338                   */
339                   select number, text = convert(char(255) not null, text)
340                   from syscomments
341                   where id = object_id(@objname)
342                   order by number, texttype, colid2, colid
343               end
344               else
345               begin
346                   /*
347                   ** Display text of non-grouped procedures and
348                   ** non-procedure objects.  Do not display a grouping
349                   ** number.
350                   */
351                   select text = convert(char(255) not null, text)
352                   from syscomments
353                   where id = object_id(@objname)
354                   order by texttype, colid2, colid
355               end
356           end
357           else /* procs with @grouping_num >= 0 */
358           begin
359               /* Display text for an individual group member */
360               select text = convert(char(255) not null, text)
361               from syscomments
362               where id = object_id(@objname)
363                   and number = @grouping_num
364               order by number, texttype, colid2, colid
365           end
366           return (0)
367       end -- }
368



exec sp_procxmode 'sp_helptext', 'AnyMode'
go

Grant Execute on sp_helptext to public
go

RESULT SETS
sp_helptext_rset_004
sp_helptext_rset_003
sp_helptext_rset_002
sp_helptext_rset_001

DEFECTS
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	126
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	292
QTYP 4 Comparison type mismatch smallint = int	292
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	363
QTYP 4 Comparison type mismatch smallint = int	363
TNOI 4 Table with no index master..syscurconfigs	master..syscurconfigs
MGTP 3 Grant to public master..syscurconfigs
MGTP 3 Grant to public sybsystemprocs..sp_helptext
MGTP 3 Grant to public sybsystemprocs..syscomments
MGTP 3 Grant to public sybsystemprocs..sysobjects
MLCH 3 Char type with length>30 char(255)	339
MLCH 3 Char type with length>30 char(255)	351
MLCH 3 Char type with length>30 char(255)	360
MNER 3 No Error Check should check return value of exec	65
MNER 3 No Error Check should check return value of exec	199
MUCO 3 Useless Code Useless Brackets in create proc	41
MUCO 3 Useless Code Useless Begin-End Pair	48
MUCO 3 Useless Code Useless Brackets	63
MUCO 3 Useless Code Useless Brackets	81
MUCO 3 Useless Code Useless Brackets	100
MUCO 3 Useless Code Useless Brackets	106
MUCO 3 Useless Code Useless Brackets	110
MUCO 3 Useless Code Useless Brackets	136
MUCO 3 Useless Code Useless Brackets	138
MUCO 3 Useless Code Useless Brackets	156
MUCO 3 Useless Code Useless Brackets	174
MUCO 3 Useless Code Useless Brackets	195
MUCO 3 Useless Code Useless Brackets	197
MUCO 3 Useless Code Useless Brackets	230
MUCO 3 Useless Code Useless Brackets	239
MUCO 3 Useless Code Useless Brackets	241
MUCO 3 Useless Code Useless Brackets	250
MUCO 3 Useless Code Useless Brackets	280
MUCO 3 Useless Code Useless Brackets	297
MUCO 3 Useless Code Useless Brackets	314
MUCO 3 Useless Code Useless Brackets	366
QAFM 3 Var Assignment from potentially many rows	124
QCRS 3 Conditional Result Set	339
QCRS 3 Conditional Result Set	351
QCRS 3 Conditional Result Set	360
QISO 3 Set isolation level	74
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered (id, number, colid2, colid, texttype) Intersection: {id}	274
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered (id, number, colid2, colid, texttype) Intersection: {number, id}	291
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered (id, number, colid2, colid, texttype) Intersection: {id}	305
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered (id, number, colid2, colid, texttype) Intersection: {id}	331
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered (id, number, colid2, colid, texttype) Intersection: {id}	341
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered (id, number, colid2, colid, texttype) Intersection: {id}	353
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered (id, number, colid2, colid, texttype) Intersection: {number, id}	362
VNRD 3 Variable is not read @procval	185
MRST 2 Result Set Marker	321
MRST 2 Result Set Marker	339
MRST 2 Result Set Marker	351
MRST 2 Result Set Marker	360
MSUB 2 Subquery Marker	169
MSUB 2 Subquery Marker	304
MTR1 2 Metrics: Comments Ratio Comments: 51%	41
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 20 = 31dec - 13exi + 2	41
MTR3 2 Metrics: Query Complexity Complexity: 135	41