DatabaseProcApplicationCreatedLinks
sybsystemprocssp_helptext  14 déc. 14Defects Propagation 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 or replace 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            /* Variables for granular permission permission checking. */
64            declare @nullarg char(1),
65                @dummy int,
66                @status int,
67                @gp_enabled int,
68                @privileged_user int,
69                @dbname varchar(255)
70    
71            select @privileged_user = 0
72    
73    
74            if (@objname IS NULL)
75            begin
76                exec sp_helptext_usage
77                return 0
78            end
79    
80            if @@trancount = 0
81            begin
82                set chained off
83            end
84    
85            set transaction isolation level 1
86    
87            select @objname_orig = @objname
88                , @opt_showsql = 'showsql'
89    
90            /* See if @objname has a group # reference. Strip it out first. */
91            select @char_index = charindex(';', @objname)
92            if (@char_index != 0)
93            begin
94                -- This is the piece of string following ';'
95                select @group_num_str = ltrim(rtrim(substring(@objname,
96                                (@char_index + 1),
97                                (datalength(@objname)
98                                - @char_index))))
99    
100               -- Retrieve just the, possibly multi-part, object name.
101               select @objname = substring(@objname, 1, (@char_index - 1))
102           end
103           /*
104           **  Make sure the @objname is local to the current database.
105           */
106           select @dbname = db_name()
107           if @objname like "%.%.%" and
108               substring(@objname, 1, charindex(".", @objname) - 1) != @dbname
109           begin
110               /* 17460, "Object must be in the current database." */
111               raiserror 17460
112               return (1)
113           end
114   
115           /*
116           **  See if @objname exists.
117           */
118           if (object_id(@objname) is NULL)
119           begin
120               /* 17461, "Object does not exist in this database." */
121               raiserror 17461
122               return (1)
123           end
124   
125           /*
126           ** If the configuration parameter 'select on syscomments.text' is set to
127           ** 0, then  the user can use sp_helptext ONLY in the following cases
128           ** 
129           **	1. if the user has sa_role
130           **	2. if the user is dbo or aliased to dbo
131           **	3. if the object is a system stored procedure
132           **	4. if the object is owned by the user
133           **
134           ** For SMP or SDC, only one row is expected.
135           */
136           select @config_parm = value
137           from master.dbo.syscurconfigs
138           where config = 258
139   
140   
141           select @issystemproc = 1 /* It is a system stored procedure */
142   
143           if @config_parm = 0
144           begin
145               /*
146               ** If granular permissions is not enabled then sa_role is required.
147               ** If granular permissions is enabled then the permission 'own database'
148               ** is required.  proc_role and proc_auditperm will also do auditing
149               ** if required. Both will also print error message if required.
150               */
151   
152               select @nullarg = NULL
153               execute @status = sp_aux_checkroleperm "dbo", "own database",
154                   @dbname, @gp_enabled output
155   
156               if (@status = 0)
157               begin
158                   select @privileged_user = 1
159               end
160   
161               /*
162               ** If the user is DBO or an account with sa_role, it's all fine
163               */
164               if (@privileged_user != 1)
165               begin
166                   if (@dbname != "master" and @dbname != "sybsystemprocs")
167                   begin
168                       /*
169                       ** System Stored Procedures can only be in master or
170                       ** sybsystemsprocs database.
171                       */
172                       select @issystemproc = 0
173                   end
174                   else
175                   begin
176                       /*
177                       ** We are in either sybsystemprocs or master database.
178                       ** See if the name starts with "sp_" and is owned by
179                       ** the DBO.
180                       */
181                       select @name = name from sysobjects where
182                           id = object_id(@objname)
183                           and uid = 1
184                       if (substring(@name, 1, 3) != "sp_")
185                           select @issystemproc = 0
186                   end
187   
188                   /*
189                   ** If it is not a system stored procedure, then it needs to be
190                   ** owned by the user.
191                   */
192                   if @issystemproc = 0
193                   begin
194                       /*
195                       ** The object needs to be owned by the user
196                       */
197                       if not exists (select name from sysobjects
198                               where uid = user_id()
199                                   and id = object_id(@objname))
200                       begin
201                           if (@gp_enabled = 0)
202                           begin
203                               raiserror 18180, "sa_role",
204                                   "sp_helptext"
205                           end
206                           else
207                           begin
208                               raiserror 18180,
209                                   "own database permission",
210                                   "sp_helptext"
211                           end
212                           return (1)
213                       end
214                   end
215               end
216               else
217               begin
218                   /*
219                   ** Audit this as a successful sa command execution if the user
220                   ** is a privileged user.
221                   */
222                   if (@gp_enabled = 0)
223                   begin
224                       select @dummy = proc_role("sa_role")
225                   end
226                   else
227                   begin
228                       select @dummy = proc_auditperm("own database",
229                               @status, @dbname)
230                   end
231               end
232           end
233   
234           -- ============================================================================ 
235           -- Check to see if user is running in improved 'showsql' mode, where they
236           -- are interested in getting text formatted as it was input. If so, call
237           -- the new sub-proc, and return from here. (Otherwise, we end up calling
238           -- in legacy mode with pre-showsql semantics for the arguments.)
239           --
240           if (@printopts IS NOT NULL)
241           begin
242               if (@printopts LIKE "%" + @opt_showsql + "%")
243               begin
244                   exec @retval = sp_showtext @objname_orig, @grouping_num
245                       , @numlines, @printopts, @trace
246                   return @retval
247               end
248               else
249               begin
250                   -- A non-NULL @printopts *must* have  at least 'showsql',
251                   -- otherwise, it's meaningless.
252                   --
253                   raiserror 19384, @opt_showsql, "@printopts"
254                   return 1
255               end
256           end
257   
258   
259           /*  Validate grouping number */
260           select @type = type from sysobjects
261           where id = object_id(@objname)
262   
263           /*
264           ** The group number is for stored procedures, but is overloaded
265           ** for the partition order of the partition condition object.
266           ** Using @grouping_num n means to get the nth partition condition
267           ** text.
268           */
269           if (@type NOT IN ('P', 'N')) and (@grouping_num is not null)
270           begin
271               /*
272               ** You cannot use a grouping number for non-procedures.
273               */
274               raiserror 18936
275               return (1)
276           end
277   
278           -- Validate the case when both "table;",  interface is used, but the
279           -- user didn't say 'showsql'. That's an error. We only allow "sproc;"
280           -- notation when sp_showtext will be called.  It's ambiguous to decide
281           -- what grouping # user means when they have specified it both ways
282           -- via "proc;3" and the @grouping_num == 5. Error out.
283           --
284           if (@group_num_str IS NOT NULL)
285           begin
286               if (@grouping_num IS NOT NULL)
287               begin
288                   -- We will really never come here as the missing 'showsql'
289                   -- has been trapped earlier, already. Just in case, report
290                   -- a generic error.
291                   --
292                   raiserror 19384, @opt_showsql, @objname_orig
293                   return 1
294               end
295               else if (patindex("%[^0-9]%", @group_num_str) = 0)
296               begin
297                   -- Allow "proc;" notation to extract SQL for just one group.
298                   select @grouping_num = convert(int, @group_num_str)
299               end
300               else
301               begin
302                   -- Reuse variable to create 2nd arg to error message.
303                   select @objname_orig = "@grouping_num='"
304                       + @group_num_str
305                       + "'"
306                   raiserror 19060, 'sp_helptext', @objname_orig
307                   return 1
308               end
309           end
310   
311           if @grouping_num is null
312           begin
313               /*
314               ** Find out how many lines of text are coming back.
315               ** and return if there are none.
316               */
317               select @text_count = count(*)
318               from syscomments
319               where id = object_id(@objname)
320   
321               if @text_count = 0
322               begin
323                   /* 17679, "There is no text for object @objname." */
324                   raiserror 17679, @objname
325                   return (1)
326               end
327           end
328           else
329           begin
330               /*
331               ** Find out how many lines of text are coming back.
332               ** and return if there are none.
333               */
334               select @text_count = count(*)
335               from syscomments
336               where id = object_id(@objname) and
337                   number = @grouping_num
338   
339               if @text_count = 0
340               begin
341                   raiserror 18937, @objname, @grouping_num
342                   return (1)
343               end
344           end
345   
346           /*
347           ** See if the object is hidden (SYSCOM_TEXT_HIDDEN will be set)
348           */
349           if exists (select 1
350                   from syscomments where (status & 1 = 1)
351                       and id = object_id(@objname))
352           begin
353               /*
354               ** 18406, "Source text for compiled object %!1
355               ** (id = %!2) is hidden."
356               */
357               select @proc_id = object_id(@objname)
358               raiserror 18406, @objname, @proc_id
359               return (1)
360           end
361   
362           /*
363           **  Return # howmany lines of text that are about to come back.
364           **  This is required by the "old" report writer.
365           */
366           select "# Lines of Text" = @text_count
367   
368           /*
369           **  Now get the text.
370           */
371           if @grouping_num is null
372           begin
373               /* Are there multiple group members? */
374               select @tot_members = count(distinct number)
375               from syscomments
376               where id = object_id(@objname)
377   
378               if @tot_members > 1
379               begin
380                   /*
381                   ** Display text of all grouped procedures.
382                   ** Include grouping number in output.
383                   */
384                   select number, text = convert(char(255) not null, text)
385                   from syscomments
386                   where id = object_id(@objname)
387                   order by number, texttype, colid2, colid
388               end
389               else
390               begin
391                   /*
392                   ** Display text of non-grouped procedures and
393                   ** non-procedure objects.  Do not display a grouping
394                   ** number.
395                   */
396                   select text = convert(char(255) not null, text)
397                   from syscomments
398                   where id = object_id(@objname)
399                   order by texttype, colid2, colid
400               end
401           end
402           else /* procs with @grouping_num >= 0 */
403           begin
404               /* Display text for an individual group member */
405               select text = convert(char(255) not null, text)
406               from syscomments
407               where id = object_id(@objname)
408                   and number = @grouping_num
409               order by number, texttype, colid2, colid
410           end
411           return (0)
412       end -- }
413   


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 138
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 337
 QTYP 4 Comparison type mismatch smallint = int 337
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 408
 QTYP 4 Comparison type mismatch smallint = int 408
 TNOI 4 Table with no index master..syscurconfigs master..syscurconfigs
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 182
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 199
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 261
 MAW1 3 Warning message on %name% sybsystemprocs..syscomments.id: Warning message on syscomments 319
 MAW1 3 Warning message on %name% sybsystemprocs..syscomments.id: Warning message on syscomments 336
 MAW1 3 Warning message on %name% sybsystemprocs..syscomments.id: Warning message on syscomments 351
 MAW1 3 Warning message on %name% sybsystemprocs..syscomments.id: Warning message on syscomments 376
 MAW1 3 Warning message on %name% sybsystemprocs..syscomments.id: Warning message on syscomments 386
 MAW1 3 Warning message on %name% sybsystemprocs..syscomments.id: Warning message on syscomments 398
 MAW1 3 Warning message on %name% sybsystemprocs..syscomments.id: Warning message on syscomments 407
 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) 384
 MLCH 3 Char type with length>30 char(255) 396
 MLCH 3 Char type with length>30 char(255) 405
 MNER 3 No Error Check should check return value of exec 76
 MNER 3 No Error Check should check return value of exec 244
 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 74
 MUCO 3 Useless Code Useless Brackets 92
 MUCO 3 Useless Code Useless Brackets 112
 MUCO 3 Useless Code Useless Brackets 118
 MUCO 3 Useless Code Useless Brackets 122
 MUCO 3 Useless Code Useless Brackets 156
 MUCO 3 Useless Code Useless Brackets 164
 MUCO 3 Useless Code Useless Brackets 166
 MUCO 3 Useless Code Useless Brackets 184
 MUCO 3 Useless Code Useless Brackets 201
 MUCO 3 Useless Code Useless Brackets 212
 MUCO 3 Useless Code Useless Brackets 222
 MUCO 3 Useless Code Useless Brackets 240
 MUCO 3 Useless Code Useless Brackets 242
 MUCO 3 Useless Code Useless Brackets 275
 MUCO 3 Useless Code Useless Brackets 284
 MUCO 3 Useless Code Useless Brackets 286
 MUCO 3 Useless Code Useless Brackets 295
 MUCO 3 Useless Code Useless Brackets 325
 MUCO 3 Useless Code Useless Brackets 342
 MUCO 3 Useless Code Useless Brackets 359
 MUCO 3 Useless Code Useless Brackets 411
 QAFM 3 Var Assignment from potentially many rows 136
 QCRS 3 Conditional Result Set 384
 QCRS 3 Conditional Result Set 396
 QCRS 3 Conditional Result Set 405
 QISO 3 Set isolation level 85
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered
(id, number, colid2, colid, texttype)
Intersection: {id}
319
 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}
336
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered
(id, number, colid2, colid, texttype)
Intersection: {id}
350
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered
(id, number, colid2, colid, texttype)
Intersection: {id}
376
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered
(id, number, colid2, colid, texttype)
Intersection: {id}
386
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered
(id, number, colid2, colid, texttype)
Intersection: {id}
398
 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}
407
 VNRD 3 Variable is not read @nullarg 152
 VNRD 3 Variable is not read @dummy 228
 VUNU 3 Variable is not used @procval 53
 MRST 2 Result Set Marker 366
 MRST 2 Result Set Marker 384
 MRST 2 Result Set Marker 396
 MRST 2 Result Set Marker 405
 MSUB 2 Subquery Marker 197
 MSUB 2 Subquery Marker 349
 MTR1 2 Metrics: Comments Ratio Comments: 51% 41
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 23 = 34dec - 13exi + 2 41
 MTR3 2 Metrics: Query Complexity Complexity: 147 41

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