DatabaseProcApplicationCreatedLinks
sybsystemprocssp_help  31 Aug 14Defects Dependencies

1     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
2     /*	4.8	1.1	06/14/90	sproc/src/help */
3     /*
4     ** Messages for "sp_help"               17570
5     **
6     ** 17460, "Object must be in the current database."
7     ** 17461, "Object does not exist in this database."
8     ** 17570, "Operating System File"
9     ** 17571, "---------------------"
10    ** 17573, "Object is Remote/External"
11    ** 17574, "-------------------------"
12    ** 17575, "Object existed prior to Omni"
13    ** 17576, "Lock scheme is Allpages" 
14    ** 17577, "Lock scheme is Datapages"
15    ** 17578, "Lock scheme is Datarows" 
16    ** 17579, "Lock scheme Unknown or Corrupted" 
17    ** 17581, "Trigger is disabled."
18    ** 17582, "Trigger is enabled."
19    ** 18571, "The attribute '%1!' is not applicable to tables with allpages lock scheme."
20    ** 17589, "computed column"
21    ** 19456, "Object is a computed column in table '%1!'."
22    ** 19457, "Object is a function-based index key in table '%1!'."
23    */
24    
25    /*
26    ** IMPORTANT NOTE:
27    ** This stored procedure uses the built-in function object_id() in the
28    ** where clause of a select query. If you intend to change this query
29    ** or use the object_id() or db_id() builtin in this procedure, please read the
30    ** READ.ME file in the $DBMS/generic/sproc directory to ensure that the rules
31    ** pertaining to object-id's and db-id's outlined there, are followed.
32    */
33    
34    create procedure sp_help
35        @objname varchar(767) = NULL /* object name we're after */
36    as
37    
38        declare @typeid int /* type of object in systypes */
39        declare @basetypeid int /* base type in systypes */
40        declare @lenfactor int /* length factor */
41        declare @sysstat smallint /* the type of the object */
42        declare @OS_file varchar(255) /* physical file for ext tab */
43        declare @msg varchar(1024)
44        declare @sptlang int
45        declare @len1 int, @len2 int, @len3 int, @len4 int, @len5 int, @len6 int, @sysstat2 int
46        declare @sqltext varchar(1024) /* SQL to execute using execute
47        ** immediate. */
48        declare @and_access int /* cache bits in OBJECT.sysstat2 */
49        declare @or_access int /* cache bits in OBJECT.sysstat2 */
50    
51        declare @sqlj_proc int /* indicates a sqlj proc */
52        declare @opt_ind_status int /* status of optimistic index lock */
53        declare @opt_ind_value int /* user input value of optimistic index lock */
54        declare @opt_ind_lock int /* Server constant for optimistic index lock */
55        declare @opt_text_dealloc int
56        declare @opt_text_value int
57        declare @opt_ind2_ascinserts int /* Server constant for ascinserts */
58        declare @text_dealloc int
59        declare @encrypted_col int /* indicates encrypted col */
60            , @decrypt_def int /* indicates decrypt default col */
61            , @inrowlob int /* status2: whether column is LOB 'in row' */
62            , @thiskey varchar(30) /* index key column of 
63        ** virtually hashed table. 
64        */
65        declare @notruncate int /* indicates no truncation for varbinary columns */
66        declare @indid int
67        declare @new_char_value varchar(255) /* hash factors of virtually 
68        ** hashed table. 
69        */
70        declare @num_keys int /* #index keys columns. */
71        declare @key_count int,
72            @msgnum int,
73            @valstat3 int,
74            @sysstat3 int,
75            @sep varchar(2),
76            @sysopt_name varchar(100)
77        declare @tab_lob_cmplvl tinyint /* LOB compression level for table */
78    
79        if @@trancount = 0
80        begin
81            set chained off
82        end
83    
84        set transaction isolation level 1
85    
86        select @sptlang = @@langid
87    
88        if @@langid != 0
89        begin
90            if not exists (
91                    select * from master.dbo.sysmessages where error
92                        between 17100 and 17109
93                        and langid = @@langid)
94                select @sptlang = 0
95        end
96    
97        set nocount on
98    
99        /*
100       **  If no @objname given, give a little info about all objects.
101       **  Note: 0x80f is the mask for sysstats (=2063decimal).
102       ** 	  800 is used by Stratus for external tables.
103       */
104   
105       select @sqlj_proc = hextoint("0x2000000")
106   
107       if @objname is NULL
108       begin
109           /*
110           ** Instead of Triggers are sub_types of trigger
111           ** type; so first check for sysstat to be 8, then build the
112           ** prefix of "instead of" for the trigger if needed.
113           */
114           select Name = o.name,
115               Owner = user_name(uid),
116               Object_type = (case
117                   when ((o.sysstat & 15) = 8)
118                   then (case
119                       when (o.type = "IT")
120                       then "instead of "
121                       else null
122                   end)
123                   else null
124               end
125               )
126               + (m.description + x.name)
127           into #sphelp1rs
128           from sysobjects o, master.dbo.spt_values v,
129               master.dbo.spt_values x, master.dbo.sysmessages m
130           where o.sysstat & 2063 = v.number
131               and ((v.type = "O" and
132                       (o.type != "XP" and (o.sysstat2 & @sqlj_proc) = 0))
133                   or (v.type = "O1" and o.type = "XP") or
134                   (v.type = "O2" and (o.sysstat2 & @sqlj_proc) != 0)
135                   or (v.type = "EK" and o.type = "EK"))
136               and v.msgnum = m.error
137               and isnull(m.langid, 0) = @sptlang
138               and ((m.error between 17100 and 17109) or
139                   (m.error between 17587 and 17589) or
140                   (m.error between 18903 and 18904) or
141                   (m.error = 17588 or m.error = 17139))
142               and x.type = "R"
143               and o.userstat & - 32768 = x.number
144           exec sp_autoformat @fulltabname = #sphelp1rs,
145               @orderby = "order by 3 desc, 1 asc"
146           drop table #sphelp1rs
147   
148           select User_type = s.name,
149               Storage_type = st.name,
150               Length = s.length,
151               Nulls = s.allownulls,
152               Default_name = object_name(s.tdefault),
153               Rule_name = object_name(s.domain),
154               Access_Rule_name = object_name(s.accessrule)
155           into #sphelp2rs
156           from systypes s, systypes st
157           where s.type = st.type
158               and s.usertype > 99
159               and st.name not in ("sysname", "longsysname", "nchar", "nvarchar")
160               and st.usertype < 100
161           exec sp_autoformat @fulltabname = #sphelp2rs,
162               @orderby = "order by 1"
163           drop table #sphelp2rs
164   
165           /* Display list of Java classes installed in this database */
166           print ""
167           select Class_name = x.xtname,
168               Jar_name = isnull(j.jname, '')
169           into #sphelp3rs
170           from sysxtypes x, sysjars j
171           where x.xtcontainer *= j.jid
172           exec sp_autoformat @fulltabname = #sphelp3rs,
173               @orderby = "order by 1"
174           drop table #sphelp3rs
175   
176           /* Display list of Java JARs installed in this database */
177           print ""
178           exec sp_autoformat @fulltabname = sysjars,
179               @selectlist = "'Jar_name' = jname",
180               @orderby = "order by 1"
181           print ""
182           return (0)
183       end
184   
185       /*
186       ** If this is a 4-part object name, mangle the name appropriately.
187       ** [Note: this must be run in tempdb for successful results.]
188       */
189       if @objname like "%.%.%.%"
190       begin
191           select @objname = str_replace(@objname, '.', '_')
192       end
193   
194       /*
195       **  Make sure the @objname is local to the current database.
196       */
197       if @objname like "%.%.%" and
198           substring(@objname, 1, charindex(".", @objname) - 1) != db_name()
199       begin
200           /* 17460, "Object must be in the current database." */
201           raiserror 17460
202           return (1)
203       end
204   
205       /*
206       **  Now check to see if the @objname is in sysobjects.  It has to be either
207       **  in sysobjects or systypes.
208       */
209       if not exists (select *
210               from sysobjects
211               where id = object_id(@objname))
212   
213       /*
214       **  It wasn't in sysobjects so we'll check in systypes.
215       */
216       begin -- {
217           select @typeid = usertype, @basetypeid = type
218           from systypes
219           where name = @objname
220   
221           /*
222           **  Time to give up -- @objname is not in sysobjects or systypes.
223           */
224           if @typeid is NULL
225           begin
226               /* 17461, "Object does not exist in this database." */
227               raiserror 17461
228               return (1)
229           end
230   
231           /*
232           ** Get length factor: types based on unichar/univarchar
233           ** are 2 bytes per character.
234           */
235           if (@basetypeid in (select type from systypes where
236                           name in ('unichar', 'univarchar')))
237               select @lenfactor = @@unicharsize
238           else
239               select @lenfactor = 1
240   
241           /*
242           ** Print help about a data type
243           */
244   
245           select Type_name = s.name,
246               Storage_type = st.name,
247               Length = s.length / @lenfactor,
248               Nulls = s.allownulls,
249               Prec = s.prec,
250               Scale = s.scale,
251               Default_name = object_name(s.tdefault),
252               Rule_name = object_name(s.domain),
253               Access_Rule_name = object_name(s.accessrule),
254               Ident = s.ident
255           into #sphelp4rs
256           from systypes s, systypes st
257           where s.usertype = @typeid
258               and s.type = st.type
259               and st.name not in ("timestamp", "sysname", "longsysname", "nchar", "nvarchar")
260               and st.usertype < 100
261   
262           exec sp_autoformat @fulltabname = #sphelp4rs,
263               @selectlist = "Type_name, Storage_type,Length,Prec,Scale,Nulls,Default_name,Rule_name,Access_Rule_name,'Identity' = Ident"
264           drop table #sphelp4rs
265   
266           return (0)
267       end -- }
268   
269       /*
270       **  It's in sysobjects so print out the information.
271       */
272   
273       /* 
274       ** Define status bit values for O2_ACCESS_CONTROL
275       ** and O2_OR_ACCESS_CONTROL.
276       */
277       select @and_access = hextoint("0x1000000"),
278           @or_access = hextoint("0x4000000")
279   
280       select Name = o.name,
281           Owner = user_name(uid),
282   
283           /*
284           ** Decode object type, and figure out if its an access rule
285           ** type. Access rules are sub-types of the rule type; so
286           ** first check for sysstat to be 7. Build the prefix of
287           ** 'AND access', or 'OR access', or '' strings. The prefix
288           ** will be concatenated to the string 'rule' as obtained
289           ** from the other tables.
290           ** Meanwhile, Instead of Triggers are sub_types of trigger
291           ** type; so first check for sysstat to be 8, then build the
292           ** prefix of "instead of" for the trigger if needed.
293           */
294           Object_type = (case
295               when ((o.sysstat & 15) = 7)
296               then (case
297                   when (((o.sysstat2 & @and_access) > 0)
298                           and ((o.sysstat2 & @or_access) > 0))
299                   then "OR access"
300                   when ((o.sysstat2 & @and_access) > 0)
301                   then "AND access"
302                   else null
303               end
304               )
305               when ((o.sysstat & 15) = 8)
306               then (case
307                   when (o.type = "IT")
308                   then "instead of "
309                   else null
310               end)
311               else null
312           end
313           )
314           + (m.description + x.name)
315           , Object_status = convert(varchar(255), ' ')
316           , Create_date = o.crdate
317       into #sphelp5rs
318       from sysobjects o, master.dbo.spt_values v,
319           master.dbo.spt_values x, master.dbo.sysmessages m
320       where o.id = object_id(@objname)
321           and o.sysstat & 2063 = v.number
322           and ((v.type = "O" and
323                   (o.type != "XP" and (o.sysstat2 & @sqlj_proc) = 0)) or
324               (v.type = "O1" and o.type = "XP") or
325               (v.type = "O2" and (o.sysstat2 & @sqlj_proc) != 0) or
326               (v.type = "EK" and o.type = "EK"))
327           and v.msgnum = m.error
328           and isnull(m.langid, 0) = @sptlang
329           and ((m.error between 17100 and 17199) or
330               (m.error between 17587 and 17589) or
331               (m.error between 18903 and 18904) or
332               (m.error = 17588))
333           and x.type = "R"
334           and o.userstat & - 32768 = x.number
335   
336       /* Set Object_status to show any special status the object may have */
337       select @sysstat3 = sysstat3
338       from sysobjects
339       where id = object_id(@objname)
340   
341       select @valstat3 = min(number), @sep = ''
342       from master.dbo.spt_values
343       where type = 'O3'
344           and number > 0
345           and number & @sysstat3 = number
346       while (@valstat3 is not null)
347       begin
348           select @msgnum = msgnum, @sysopt_name = name
349           from master.dbo.spt_values
350           where type = 'O3' and number = @valstat3
351   
352           select @sysopt_name = isnull(description, @sysopt_name)
353           from master.dbo.sysmessages
354           where error = @msgnum
355               and isnull(langid, 0) = @sptlang
356   
357           update #sphelp5rs
358           set Object_status = ltrim(Object_status + @sep + @sysopt_name)
359           from #sphelp5rs a, master.dbo.spt_values v, sysobjects o
360           where a.Name = o.name
361               and o.sysstat & 15 = 3
362               and o.sysstat3 & @valstat3 = @valstat3
363   
364           select @valstat3 = min(number), @sep = ', '
365           from master.dbo.spt_values
366           where type = 'O3'
367               and number > @valstat3
368               and number & @sysstat3 = number
369       end
370   
371       update #sphelp5rs
372       set Object_status = (select description
373               from master.dbo.sysmessages
374               where error = 17661
375                   and isnull(langid, 0) = @sptlang)
376       where datalength(Object_status) < 2
377   
378       exec sp_autoformat @fulltabname = #sphelp5rs
379       drop table #sphelp5rs
380   
381   
382       /*
383       **  Objects have the following value for sysstat & 15:
384       **	0 - trigger
385       **	1 - system table
386       **	2 - view
387       **	3 - user table
388       **	4 - sproc
389       **	5 - predicated privilege
390       **	6 - default
391       **	7 - rule
392       **	8 - trigger
393       **	9 - referential constraint
394       **	10 - sql function  
395       **      12 - sqlj function
396       **      13 - computed column
397       **	
398       **
399       **  If the object is a system table, view, or user table, we want to check
400       **  out the objects columns here.
401       */
402   
403       select @sysstat = sysstat, @sysstat2 = sysstat2
404       from sysobjects
405       where id = object_id(@objname)
406   
407       /*
408       **  Fix of bug 91669:
409       **  For the current design, a view having nameless column may be created,
410       **  e.g. create view view1 as select sum(column1) from table1. 
411       **  In this case, c.name is NULL. Using builtin function isnull() to  
412       **  make the following query work. 
413       */
414   
415       select @encrypted_col = hextoint("0x00000080")
416           , @decrypt_def = hextoint("0x00001000")
417           , @inrowlob = hextoint("0x00040000")
418   
419       -- Report on the following "table" objects:
420       -- 	1: System tables
421       -- 	2: Views
422       -- 	3: User tables
423       --
424       if (@sysstat & 15) in (1, 2, 3)
425           select @notruncate = hextoint("0x00200000")
426       begin
427           select Column_name = isnull(c.name, 'NULL'),
428               Col_order = colid,
429               Type = isnull(convert(char(30), x.xtname),
430                   isnull(convert(char(30),
431                       get_xtypename(c.xtype, c.xdbid)),
432                       t.name)),
433               Length = c.length,
434               In_row_Len = c.inrowlen,
435               Prec = c.prec,
436               Scale = c.scale,
437               Nulls = convert(bit, (c.status & 8)),
438               Not_compressed = convert(bit, (isnull(c.status2, 0) & 131072)),
439               Lob_compression_level = c.lobcomp_lvl,
440               Default_name = object_name(c.cdefault),
441               Rule_name = object_name(c.domain),
442               Access_Rule_name = object_name(c.accessrule),
443               Computed_Column_object =
444               case when (c.status3 & 1) = 1
445                   then object_name(c.computedcol) +
446                   " (functional index key)"
447                   when (c.status2 & 32) = 32
448                   then object_name(c.computedcol) + " (materialized)"
449                   when (c.status2 & 16) = 16
450                   then object_name(c.computedcol) + " (virtual)"
451                   else object_name(c.computedcol)
452               end,
453               rtype = t.type, utype = t.usertype, xtype = c.xtype,
454               Ident = convert(bit, (c.status & 0x80)),
455               Encrypted =
456               case when (c.status2 is null) then 0
457                   when (c.status2 & @encrypted_col) > 0 then 1
458               end,
459               Decrypt_Default_name =
460               case when (c.status2 is null) then NULL
461                   when (c.status2 & @decrypt_def) > 0 then
462                       (select object_name(a.object)
463                       from sysattributes a
464                       where a.class = 25
465                           and a.attribute = 1
466                           and a.object_info1 = c.id
467                           and a.object_info2 = c.colid)
468                   else NULL
469               end,
470               Object_storage =
471               case
472                   when ((isnull(c.status2, 0) & @inrowlob) != 0)
473                   then "in row"
474                   when (c.xstatus is null) then NULL
475                   when (c.xstatus & 1) = 1 then "off row"
476                   else "in row "
477               end,
478               Varbinary_is_truncated =
479               case when (c.status2 is null) then NULL
480                   when (c.status2 & @notruncate) > 0 then 0
481                   else 1
482               end
483           into #helptype
484           from syscolumns c, systypes t, sysxtypes x
485           where c.id = object_id(@objname)
486               and c.usertype *= t.usertype
487               and c.xtype *= x.xtid
488   
489           /* 
490           ** We truncate extended type names >30 characters to 30,
491           ** and print them with a trailing "+" character.
492           */
493           update #helptype
494           set Type = substring(Type, 1, 29) + "+"
495           where xtype is not null
496               and substring(Type, 29, 1) != " "
497   
498           /* Handle National Characters */
499           update #helptype
500           set Length = Length / @@ncharsize
501           where (rtype = 47 and utype = 24)
502               or (rtype = 39 and utype = 25)
503   
504           /* Handle unichar/univarchar */
505           update #helptype
506           set Length = Length / @@unicharsize
507           where rtype in (select type from systypes
508                   where name in ('unichar', 'univarchar'))
509   
510           /* Handle unsigned types by outputing user syntax */
511           update #helptype
512           set Type = "unsigned " +
513               substring(Type, charindex("u", Type) + 1, 30)
514           where utype in (44, 45, 46)
515   
516           /*
517           ** Construct the SQL query against #helptype. Be careful not to
518           ** exceed 255 characters, or the string will get truncated.
519           */
520           select @sqltext = "Column_name,Type, Length"
521   
522           /* Display in-row length only if there are any in-row LOBs */
523           if exists (select 1 from #helptype
524                   where In_row_Len is not null)
525           begin
526               /* Convert bytes to unichars for length of unitext */
527               update #helptype
528               set In_row_Len = In_row_Len / @@unicharsize
529               where rtype in (select type from systypes
530                       where name = 'unitext')
531   
532               select @sqltext = @sqltext + ",In_row_Len "
533           end
534   
535           select @sqltext = @sqltext
536               + ", Prec,Scale,Nulls,Not_compressed,Default_name,Rule_name,Access_Rule_name,Computed_Column_object,'Identity' = Ident"
537   
538           /* Display the Object_storage only if there are object columns. */
539           if exists (select * from #helptype
540                   where Object_storage is not null)
541           begin
542               select @sqltext = @sqltext + ", Object_storage "
543           end
544   
545           /* 
546           ** Display the Encrypted column status only if there are encrypted
547           ** columns.
548           */
549           if exists (select * from #helptype
550                   where Encrypted != 0)
551           begin
552               select @sqltext = @sqltext + ", Encrypted "
553           end
554   
555   
556           /* 
557           ** Display the Decrypt_Default_name only if there 
558           ** are encrypted columns that have decrypt default values
559           */
560           if exists (select * from #helptype
561                   where Decrypt_Default_name is not null)
562           begin
563               select @sqltext = @sqltext + ", Decrypt_Default_name"
564           end
565   
566           /* 
567           ** Display Lob compression level only if there are compressed LOB
568           ** columns.
569           */
570           if exists (select * from #helptype
571                   where Lob_compression_level is not null)
572           begin
573               select @sqltext = @sqltext + ", Lob_compression_level"
574           end
575   
576           /* 
577           ** Display the varbinary truncation only if there 
578           ** is any varbinary column.
579           */
580           if exists (select * from #helptype where Type = "varbinary" or (Type = "binary" and Nulls = 1))
581           begin
582               select @sqltext = @sqltext + ", Varbinary_is_truncated"
583   
584               /* Update the field non-varbinary types */
585               update #helptype set Varbinary_is_truncated = NULL
586               where (Type != "varbinary" and (Type != "binary" or Nulls = 0))
587                   or Computed_Column_object is not null
588           end
589   
590           exec sp_autoformat @fulltabname = #helptype,
591               @selectlist = @sqltext,
592               @orderby = "order by Col_order asc"
593   
594           drop table #helptype
595       end
596   
597       /* 
598       ** If this is a table object that has computed columns, display the
599       ** computed column information.
600       */
601       if (@sysstat & 15) in (1, 3)
602       begin
603           if exists (select 1 from syscolumns where id = object_id(@objname)
604                       and computedcol is not null and (status3 & 1) != 1)
605           begin
606               print ""
607               execute dbo.sp_helpcomputedcolumn @objname, 0
608           end
609       end
610   
611       /*
612       **  For procedures and sqlj functions, the parameters of the procedures 
613       **  are stored in syscolumns.
614       */
615       if @sysstat & 15 in (4, 10, 12)
616       begin
617           exec sp_help_params @objname
618       end
619   
620       /*
621       **  If the object is an external table, show which OS file it's using.
622       */
623       if @sysstat & 2063 = 2051
624       begin
625           select @OS_file = name from sysindexes
626           where id = object_id(@objname)
627               and indid in (0, 1)
628           /*
629           ** 17570, "Operating System File"
630           ** 17571, "---------------------"
631           */
632           print ""
633           exec sp_getmessage 17570, @msg out
634           print @msg
635           exec sp_getmessage 17571, @msg out
636           print @msg
637           print @OS_file
638           print ""
639       end
640       /*
641       **  If the object is an Omni-managed table, show its storage location.
642       */
643       if (@sysstat2 & 1024 = 1024)
644       begin
645           declare @dbname varchar(255),
646               @site varchar(255),
647               @owner varchar(255),
648               @tabname varchar(255),
649               @retcode int
650   
651           exec @retcode = sp_namecrack @objname, @site output, @dbname output,
652               @owner output, @tabname output
653   
654           select @OS_file = char_value from sysattributes
655           where class = 9 and attribute = 1 and
656               object_cinfo = @tabname
657   
658           /*
659           **  17573, "Object is Remote/External"
660           **  17574, "-------------------------"
661           */
662           print ""
663           exec sp_getmessage 17573, @msg out
664           print @msg
665           exec sp_getmessage 17574, @msg out
666           print @msg
667           print @OS_file
668           print ""
669   
670           if (@sysstat2 & 2048 = 2048)
671           begin
672               /*
673               ** 17575, "Object existed prior to Omni"
674               */
675               exec sp_getmessage 17575, @msg out
676               print @msg
677               print ""
678           end
679       end
680   
681       /* 
682       **  If the object is a table, display sysattributes information
683       **  if there is any.  It could be in the current database under
684       **  type "T".
685       */
686   
687       if @sysstat & 15 in (1, 3)
688       begin
689           /*  Create temporary table for sysattributes data */
690           create table #sphelpattr
691           (
692               class varchar(255),
693               class_id smallint,
694               attribute varchar(255),
695               attribute_id smallint,
696               int_value int NULL,
697               char_value varchar(255) NULL,
698               comments varchar(255) NULL
699           )
700   
701           /* 
702           **  The join with master..sysattributes here is to
703           **  get the string descriptions for the class and attribute.
704           **  These should never be more than 30 chars, so it's okay to
705           **  truncate them.
706           */
707   
708           insert #sphelpattr(class, class_id, attribute, attribute_id, int_value,
709               char_value, comments)
710           select c.char_value,
711               t.class,
712               a.char_value,
713               t.attribute,
714               t.int_value, t.char_value, t.comments
715           from sysattributes t, master.dbo.sysattributes c,
716               master.dbo.sysattributes a
717           where t.object_type = "T"
718               and t.object = object_id(@objname)
719               and c.class = 0 and c.attribute = 0
720               and a.class = 0 and a.attribute = 1
721               and t.class = c.object
722               and t.class = a.object
723               and t.attribute = a.object_info1
724   
725           /*
726           ** If the table is virtually hashed, then print the list of index key
727           ** columns and their correspondig hash factors as char_value.
728           */
729           if (exists (select attribute from #sphelpattr
730                       where attribute = 'hash key factors'))
731           begin
732               /* The table is Virtually Hashed */
733               exec sp_getmessage 19586, @msg out
734               print @msg
735               print ""
736   
737               select @new_char_value = ""
738               select @num_keys = keycnt from sysindexes
739               where id = object_id(@objname)
740   
741               select @key_count = 1
742   
743               select @indid = min(indid)
744               from sysindexes
745               where id = object_id(@objname)
746                   and indid > 0
747                   and indid < 255
748   
749               set nocount on
750   
751               /*
752               ** Get the list of index key columns.
753               */
754               while (@key_count <= @num_keys)
755               begin
756                   select @thiskey =
757                       index_col(@objname, @indid, @key_count)
758   
759                   if (@thiskey is NULL)
760                   begin
761                       break
762                   end
763   
764                   if (@key_count > 1)
765                   begin
766                       select @new_char_value = @new_char_value + ", "
767                   end
768   
769                   select @new_char_value = @new_char_value + @thiskey
770   
771                   /*
772                   ** In current 'char_value', we have an array of hash
773                   ** factors of type 'double' that was written to
774                   ** 'char_value' as byte string.
775                   ** Hence to get the array of hash factors back:
776                   **
777                   ** 1. Divide 'char_value' into disjoint substrings,
778                   **    each of length 8 (sizeof double).
779                   ** 2. Convert the substring into binary type.
780                   ** 3. Convert the binary value into 'double'.
781                   **
782                   ** Once obtained the value, convert it to character
783                   ** type and append to @new_char_value
784                   */
785                   select @new_char_value = @new_char_value
786                       + ":" +
787                       convert(varchar(255),
788                       convert(double precision,
789                       convert(binary,
790                       substring(char_value, @key_count * 8 - 7, 8))))
791                   from #sphelpattr
792                   where attribute = 'hash key factors'
793   
794                   select @key_count = @key_count + 1
795               end
796   
797   
798               /*
799               ** Append the 'max_hash_key' to @new_char_value
800               */
801               select @new_char_value = @new_char_value + ", max_hash_key"
802   
803               select @new_char_value = @new_char_value +
804                   "=" +
805                   convert(varchar(255),
806                   convert(double precision,
807                   convert(binary,
808                   substring(char_value, @key_count * 8 - 7, 8))))
809               from #sphelpattr
810               where attribute = 'hash key factors'
811   
812               update #sphelpattr set char_value = @new_char_value
813               where attribute = 'hash key factors'
814           end
815   
816           /*
817           ** It's possible a cache is deleted without doing an unbind first. After
818           ** a server reboot the binding is marked 'invalid' (int_value = 0).
819           ** If we have such an invalid binding, don't show it in the output.
820           */
821           delete from #sphelpattr
822           where class_id = 3
823               and attribute_id = 0
824               and int_value = 0
825   
826           if exists (select * from #sphelpattr)
827           begin
828               exec sp_autoformat @fulltabname = #sphelpattr,
829                   @selectlist = "'attribute_class' = class, attribute, int_value,char_value, comments"
830           end
831           drop table #sphelpattr
832       end
833   
834       /* 
835       **  If the object is a procedure, display sysattributes information
836       **  if there is any.
837       */
838   
839       if @sysstat & 15 = 4
840       begin
841           if exists (select * from sysattributes
842                   where object_type = "P"
843                       and object = object_id(@objname))
844           begin
845               /* 
846               **  The join with master..sysattributes here is to
847               **  get the string descriptions for the class 
848               **  (master.dbo.sysattributes c) and attribute
849               **  (master.dbo.sysattributes a).
850               */
851   
852               select attribute_class =
853                   convert(varchar(512), c.char_value),
854                   attribute = convert(varchar(512), a.char_value),
855                   t.int_value,
856                   char_value = convert(varchar(512), t.char_value),
857                   t.comments
858               into #sphelp6rs
859               from sysattributes t, master.dbo.sysattributes c,
860                   master.dbo.sysattributes a
861               where t.object_type = "P"
862                   and t.object = object_id(@objname)
863                   and c.class = 0 and c.attribute = 0
864                   and a.class = 0 and a.attribute = 1
865                   and t.class = c.object
866                   and t.class = a.object
867                   and t.attribute = a.object_info1
868   
869               exec sp_autoformat @fulltabname = #sphelp6rs
870               drop table #sphelp6rs
871           end
872       end
873   
874       /*
875       **  If the object is a table, check out the indexes.
876       */
877       if @sysstat & 15 in (1, 3)
878           execute dbo.sp_helpindex @objname
879   
880       /*
881       **  If the object is a table or view, check out the keys.
882       */
883       if @sysstat & 15 in (1, 2, 3)
884           execute dbo.sp_helpkey @objname
885   
886       /*
887       **  If the object is a table, check out the slices/partitions
888       */
889       if @sysstat & 15 in (1, 3)
890           execute dbo.sp_helpartition @objname
891   
892       /*
893       ** If the object is a trigger, it is either enabled or disabled
894       */
895       if @sysstat & 15 in (0, 8)
896       begin
897           /*
898           ** 1048676 <==> 0x100000 <==> insert trigger disabled
899           ** 2097152 <==> 0x200000 <==> delete trigger disabled
900           ** 4194304 <==> 0x400000 <==> update trigger disabled
901           */
902           if exists (select 1 from sysobjects trig, sysobjects tab
903                   where trig.id = object_id(@objname)
904                       and trig.deltrig = tab.id
905                       and ((trig.id = tab.deltrig and tab.sysstat2 & 2097152 <> 0)
906                           or (trig.id = tab.updtrig and tab.sysstat2 & 4194304 <> 0)
907                           or (trig.id = tab.instrig and tab.sysstat2 & 1048576 <> 0)))
908               exec sp_getmessage 17581, @msg out
909           else
910               exec sp_getmessage 17582, @msg out
911   
912           print @msg
913       end
914   
915       /*
916       ** If the object is a table, display the table level LOB compression level
917       */
918       if @sysstat & 15 in (1, 3)
919       begin
920           select @tab_lob_cmplvl = lobcomp_lvl
921           from sysobjects
922           where id = object_id(@objname)
923   
924           select @msg = "Table LOB compression level " + convert(varchar(3), @tab_lob_cmplvl)
925           print @msg
926       end
927   
928       /*
929       ** Print the lock scheme information for the table objects
930       */
931       if @sysstat & 15 in (1, 3)
932       begin
933           /*
934           ** the bits 0x2000, 0x4000 & 0x8000 represents any 
935           ** explicit lock scheme bits that can be set, so
936           ** get them out ( 0x2000 + 0x4000 + 0x8000 = 57344)
937           */
938           select @sysstat2 = (sysstat2 & 57344)
939           from sysobjects
940           where id = object_id(@objname)
941           /*
942           ** The value 0, refers that no lock scheme is 
943           ** specified (old style tables) so they support only
944           ** allpages locking
945           */
946           if (@sysstat2 in (0, 8192, 16384, 32768))
947           begin
948               if (@sysstat2 = 8192 or @sysstat2 = 0)
949               begin
950                   /* 17576, "Lock scheme is Allpages" */
951                   exec sp_getmessage 17576, @msg out
952                   print @msg
953                   /* 18571, "The attribute '%1!' is */
954                   /* not applicable to tables with  */
955                   /* allpages lock scheme."         */
956                   exec sp_getmessage 18571, @msg out
957                   print @msg, 'exp_row_size'
958                   /* 18571, "The attribute '%1!' is */
959                   /* not applicable to tables with  */
960                   /* allpages lock scheme."         */
961                   exec sp_getmessage 18571, @msg out
962                   print @msg, 'concurrency_opt_threshold'
963               end
964   
965               if (@sysstat2 = 16384)
966               begin
967                   /* 17577, "Lock scheme is Datapages" */
968                   exec sp_getmessage 17577, @msg out
969                   print @msg
970   
971                   /* 18983, "The '%1!' attribute is not applicable 
972                   ** to tables with datarow or datapage lock schemes.
973                   */
974                   exec sp_getmessage 18983, @msg out
975                   print @msg, 'ascinserts'
976               end
977   
978               if (@sysstat2 = 32768)
979               begin
980                   /* 17578, "Lock scheme is Datarows" */
981                   exec sp_getmessage 17578, @msg out
982                   print @msg
983   
984                   /* 18983, "The '%1!' attribute is not applicable 
985                   ** to tables with datarow or datapage lock schemes.
986                   */
987                   exec sp_getmessage 18983, @msg out
988                   print @msg, 'ascinserts'
989               end
990   
991               /* Server defined value for ascinserts 
992               ** if ascinserts is set, then master..sysindexes.status2=64 
993               */
994               select @opt_ind2_ascinserts = 64
995   
996               print " "
997               select exp_rowsize "exp_row_size",
998                   res_page_gap "reservepagegap",
999                   fill_factor "fillfactor",
1000                  maxrowsperpage "max_rows_per_page",
1001                  isnull(identitygap, 0) "identity_gap",
1002                  (case when ((@opt_ind2_ascinserts & status2) > 0)
1003                      then 1
1004                      else 0
1005                  end
1006                  ) "ascinserts"
1007              from sysindexes
1008              where id = object_id(@objname)
1009                  and indid <= 1
1010  
1011              /* Server defined constant for optimistic index lock */
1012              select @opt_ind_lock = 268435456
1013  
1014              /* Server defined constant for dealloc first txtpg */
1015              select @text_dealloc = hextoint("0x020000000")
1016  
1017              select @opt_ind_status = (sysstat2 & @opt_ind_lock),
1018                  @opt_text_dealloc = (sysstat2 & @text_dealloc)
1019              from sysobjects where id = object_id(@objname)
1020  
1021              if (@opt_ind_status = 0)
1022                  select @opt_ind_value = 0
1023              else
1024                  select @opt_ind_value = 1
1025  
1026              if (@opt_text_dealloc = 0)
1027                  select @opt_text_value = 0
1028              else
1029                  select @opt_text_value = 1
1030  
1031              /*
1032              ** Currently, the following information will be same
1033              ** for all partitions, only need to print once for
1034              ** one of the partitions
1035              */
1036              select distinct
1037                  conopt_thld "concurrency_opt_threshold",
1038                  @opt_ind_value "optimistic_index_lock",
1039                  @opt_text_value "dealloc_first_txtpg"
1040              from systabstats
1041              where id = object_id(@objname)
1042                  and indid <= 1
1043  
1044              return (0)
1045          end
1046          else
1047          begin
1048              /* 17579, "Lock scheme Unknown or Corrupted" */
1049              exec sp_getmessage 17579, @msg out
1050              print @msg
1051              return (1)
1052          end
1053      end
1054  
1055      /* 
1056      **  If the object is computed column, display 
1057      **  computed column information.
1058      **  We just simply follow the way sp_helpcomputedcolumn works.
1059      */
1060      if (@sysstat & 15) = 13
1061      begin
1062          declare @count int, @total int, @row_id int, @config_parm int, @mode int, @tab_name varchar(767)
1063  
1064          select @tab_name = object_name(id), @mode = status3 & 1
1065          from syscolumns
1066          where computedcol = object_id(@objname)
1067  
1068          /*
1069          ** Create temporary table to stored computed column info 
1070          */
1071          create table #helpcpc(colname varchar(255), computedcol int,
1072              property varchar(15) null, row_id numeric identity)
1073          /* 
1074          ** Get info on computed columns
1075          */
1076          if @mode != 1
1077          begin
1078              insert into #helpcpc(colname, computedcol, property)
1079              select name,
1080                  computedcol,
1081                  case when (status2 & 32 = 32) then "materialized"
1082                      else "virtual" end
1083              from syscolumns where
1084                  computedcol = object_id(@objname) and
1085                  status3 & 1 != 1
1086              order by colid
1087          end
1088          /*
1089          ** Get Info on functional index keys
1090          */
1091          else
1092          begin
1093              insert into #helpcpc(colname, computedcol)
1094              select name, computedcol from syscolumns where
1095                  computedcol = object_id(@objname) and
1096                  status3 & 1 = 1
1097              order by colid
1098          end
1099  
1100          /*
1101          ** Display the header
1102          */
1103          if @mode != 1
1104          begin
1105              exec sp_getmessage 19456, @msg output
1106          end
1107          else
1108          begin
1109              exec sp_getmessage 19457, @msg output
1110          end
1111          print ""
1112          print @msg, @tab_name
1113          print ""
1114  
1115          /*
1116          ** If the configuration parameter 'allow select on syscomments.text' 
1117          ** is set to 0, then the user can access the text ONLY in the 
1118          ** following cases
1119          ** 
1120          **	1. if the user has sa_role
1121          **	2. if the object is owned by the user
1122          **
1123          */
1124          select @config_parm = value
1125          from master.dbo.syscurconfigs
1126          where config = 258
1127  
1128          if @config_parm = 0 and user_id() != 1
1129          begin
1130              /*
1131              ** The object needs to be owned by the user
1132              */
1133              if not exists (select name from sysobjects
1134                      where uid = user_id()
1135                          and id = object_id(@objname))
1136              begin
1137                  /* 
1138                  ** Inform the user the text cannot be displayed and 
1139                  ** print the rest info before return
1140                  */
1141                  if @mode != 1
1142                  begin
1143                      exec sp_getmessage 19334, @msg output
1144                      print @msg
1145                      exec sp_autoformat #helpcpc, "'Column_Name' = colname,
1146  					Property = property"
1147                  end
1148                  else
1149                  begin
1150                      exec sp_getmessage 19335, @msg output
1151                      print @msg
1152                      exec sp_autoformat #helpcpc,
1153                          "'Internal_Index_Key_Name' = colname"
1154                  end
1155  
1156                  drop table #helpcpc
1157                  return (0)
1158              end
1159          end
1160  
1161          /*
1162          ** Now display the text
1163          */
1164          create table #helptxt(text varchar(255), row_id numeric(10) identity)
1165          create table #helpname(colname varchar(255), property varchar(15) null)
1166  
1167          select @count = 1
1168          select @total = max(row_id) + 1 from #helpcpc
1169          while @count < @total
1170          begin
1171              insert into #helpname(colname, property)
1172              select colname, property from #helpcpc where row_id = @count
1173  
1174              if @mode != 1
1175              begin
1176                  exec sp_autoformat #helpname, "'Column_Name' = colname, 
1177  					Property = property"
1178              end
1179              else
1180              begin
1181                  exec sp_autoformat #helpname,
1182                      "'Internal_Index_Key_Name' = colname"
1183              end
1184  
1185              /*
1186              ** See if the object is hidden (SYSCOM_TEXT_HIDDEN will be set)
1187              */
1188              if exists (select 1 from syscomments m, #helpcpc h where
1189                          (m.status & 1 = 1) and
1190                          m.id = h.computedcol and
1191                          h.row_id = @count)
1192              begin
1193                  exec sp_getmessage 19337, @msg output
1194                  print @msg
1195                  print ""
1196                  delete #helpname
1197                  select @count = @count + 1
1198                  continue
1199              end
1200  
1201              insert #helptxt(text) select text from syscomments, #helpcpc
1202              where row_id = @count and id = computedcol order by colid2, colid
1203  
1204              print ""
1205              if @mode != 1
1206              begin
1207                  exec sp_autoformat #helptxt, "Text = text"
1208              end
1209              else
1210              begin
1211                  select @row_id = min(row_id) from #helptxt
1212                  update #helptxt set text = right (text, char_length(text) - 4)
1213                  where row_id = @row_id
1214  
1215                  select @row_id = max(row_id) from #helptxt
1216                  update #helptxt set text = left (text, char_length(text) - 13)
1217                  where row_id = @row_id
1218                  exec sp_autoformat #helptxt, "Expression = text"
1219              end
1220              print ""
1221  
1222              select @count = @count + 1
1223              delete #helpname
1224              delete #helptxt
1225          end
1226  
1227          drop table #helpcpc, #helpname, #helptxt
1228      end
1229  
1230      return (0)
1231  
1232  


exec sp_procxmode 'sp_help', 'AnyMode'
go

Grant Execute on sp_help to public
go
RESULT SETS
sp_help_rset_002
sp_help_rset_001

DEFECTS
 QCAR 6 Cartesian product between tables #sphelp5rs a and [master..spt_values v] 359
 QJWI 5 Join or Sarg Without Index 157
 QJWI 5 Join or Sarg Without Index 171
 QJWI 5 Join or Sarg Without Index 258
 QJWI 5 Join or Sarg Without Index 486
 QJWI 5 Join or Sarg Without Index 487
 QJWI 5 Join or Sarg Without Index 1066
 QJWI 5 Join or Sarg Without Index 1084
 QJWI 5 Join or Sarg Without Index 1095
 MEST 4 Empty String will be replaced by Single Space 166
 MEST 4 Empty String will be replaced by Single Space 168
 MEST 4 Empty String will be replaced by Single Space 177
 MEST 4 Empty String will be replaced by Single Space 181
 MEST 4 Empty String will be replaced by Single Space 341
 MEST 4 Empty String will be replaced by Single Space 606
 MEST 4 Empty String will be replaced by Single Space 632
 MEST 4 Empty String will be replaced by Single Space 638
 MEST 4 Empty String will be replaced by Single Space 662
 MEST 4 Empty String will be replaced by Single Space 668
 MEST 4 Empty String will be replaced by Single Space 677
 MEST 4 Empty String will be replaced by Single Space 735
 MEST 4 Empty String will be replaced by Single Space 737
 MEST 4 Empty String will be replaced by Single Space 1111
 MEST 4 Empty String will be replaced by Single Space 1113
 MEST 4 Empty String will be replaced by Single Space 1195
 MEST 4 Empty String will be replaced by Single Space 1204
 MEST 4 Empty String will be replaced by Single Space 1220
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MINU 4 Unique Index with nullable columns master..sysmessages master..sysmessages
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysattributes sybsystemprocs..sysattributes
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysjars sybsystemprocs..sysjars
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysxtypes sybsystemprocs..sysxtypes
 MTYP 4 Assignment type mismatch @OS_file: varchar(255) = varchar(768) 654
 MTYP 4 Assignment type mismatch class: varchar(255) = varchar(768) 710
 MTYP 4 Assignment type mismatch attribute: varchar(255) = varchar(768) 712
 MTYP 4 Assignment type mismatch char_value: varchar(255) = varchar(768) 714
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 144
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 161
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 172
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 178
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 262
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 378
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 590
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 828
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 869
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 1145
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 1152
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 1176
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 1181
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 1207
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 1218
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_info2, object_info1}
Uncovered: [object_type, object, object_info3, object_cinfo]
466
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object}
Uncovered: [object_type, object_info1, object_info2, object_info3, object_cinfo]
721
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object, object_info1}
Uncovered: [object_type, object_info2, object_info3, object_cinfo]
722
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object}
842
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object}
Uncovered: [object_type, object_info1, object_info2, object_info3, object_cinfo]
865
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object, object_info1}
Uncovered: [object_type, object_info2, object_info3, object_cinfo]
866
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 93
 QTYP 4 Comparison type mismatch smallint = int 93
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 158
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 160
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 257
 QTYP 4 Comparison type mismatch smallint = int 257
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 260
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 464
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 465
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 467
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 501
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 501
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 502
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 502
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 655
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 719
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 720
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 721
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 722
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 723
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 746
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 747
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 822
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 823
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 863
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 864
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 865
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 866
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 867
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1009
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1042
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1126
 QTYP 4 Comparison type mismatch Comparison type mismatch: numeric(18,0) vs int 1172
 QTYP 4 Comparison type mismatch Comparison type mismatch: numeric(18,0) vs int 1191
 QTYP 4 Comparison type mismatch Comparison type mismatch: numeric(18,0) vs int 1202
 QTYP 4 Comparison type mismatch Comparison type mismatch: numeric(10,0) vs int 1213
 QTYP 4 Comparison type mismatch Comparison type mismatch: numeric(10,0) vs int 1217
 TNOI 4 Table with no index master..syscurconfigs master..syscurconfigs
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 454
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..syscurconfigs  
 MGTP 3 Grant to public master..sysmessages  
 MGTP 3 Grant to public sybsystemprocs..sp_help  
 MGTP 3 Grant to public sybsystemprocs..sysattributes  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..syscomments  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MGTP 3 Grant to public sybsystemprocs..sysjars  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..systabstats  
 MGTP 3 Grant to public sybsystemprocs..systypes  
 MGTP 3 Grant to public sybsystemprocs..sysxtypes  
 MNER 3 No Error Check should check @@error after select into 114
 MNER 3 No Error Check should check return value of exec 144
 MNER 3 No Error Check should check @@error after select into 148
 MNER 3 No Error Check should check return value of exec 161
 MNER 3 No Error Check should check @@error after select into 167
 MNER 3 No Error Check should check return value of exec 172
 MNER 3 No Error Check should check return value of exec 178
 MNER 3 No Error Check should check @@error after select into 245
 MNER 3 No Error Check should check return value of exec 262
 MNER 3 No Error Check should check @@error after select into 280
 MNER 3 No Error Check should check @@error after update 357
 MNER 3 No Error Check should check @@error after update 371
 MNER 3 No Error Check should check return value of exec 378
 MNER 3 No Error Check should check @@error after select into 427
 MNER 3 No Error Check should check @@error after update 493
 MNER 3 No Error Check should check @@error after update 499
 MNER 3 No Error Check should check @@error after update 505
 MNER 3 No Error Check should check @@error after update 511
 MNER 3 No Error Check should check @@error after update 527
 MNER 3 No Error Check should check @@error after update 585
 MNER 3 No Error Check should check return value of exec 590
 MNER 3 No Error Check should check return value of exec 607
 MNER 3 No Error Check should check return value of exec 617
 MNER 3 No Error Check should check return value of exec 633
 MNER 3 No Error Check should check return value of exec 635
 MNER 3 No Error Check should check return value of exec 651
 MNER 3 No Error Check should check return value of exec 663
 MNER 3 No Error Check should check return value of exec 665
 MNER 3 No Error Check should check return value of exec 675
 MNER 3 No Error Check should check @@error after insert 708
 MNER 3 No Error Check should check return value of exec 733
 MNER 3 No Error Check should check @@error after update 812
 MNER 3 No Error Check should check @@error after delete 821
 MNER 3 No Error Check should check return value of exec 828
 MNER 3 No Error Check should check @@error after select into 852
 MNER 3 No Error Check should check return value of exec 869
 MNER 3 No Error Check should check return value of exec 878
 MNER 3 No Error Check should check return value of exec 884
 MNER 3 No Error Check should check return value of exec 890
 MNER 3 No Error Check should check return value of exec 908
 MNER 3 No Error Check should check return value of exec 910
 MNER 3 No Error Check should check return value of exec 951
 MNER 3 No Error Check should check return value of exec 956
 MNER 3 No Error Check should check return value of exec 961
 MNER 3 No Error Check should check return value of exec 968
 MNER 3 No Error Check should check return value of exec 974
 MNER 3 No Error Check should check return value of exec 981
 MNER 3 No Error Check should check return value of exec 987
 MNER 3 No Error Check should check return value of exec 1049
 MNER 3 No Error Check should check @@error after insert 1078
 MNER 3 No Error Check should check @@error after insert 1093
 MNER 3 No Error Check should check return value of exec 1105
 MNER 3 No Error Check should check return value of exec 1109
 MNER 3 No Error Check should check return value of exec 1143
 MNER 3 No Error Check should check return value of exec 1145
 MNER 3 No Error Check should check return value of exec 1150
 MNER 3 No Error Check should check return value of exec 1152
 MNER 3 No Error Check should check @@error after insert 1171
 MNER 3 No Error Check should check return value of exec 1176
 MNER 3 No Error Check should check return value of exec 1181
 MNER 3 No Error Check should check return value of exec 1193
 MNER 3 No Error Check should check @@error after delete 1196
 MNER 3 No Error Check should check @@error after insert 1201
 MNER 3 No Error Check should check return value of exec 1207
 MNER 3 No Error Check should check @@error after update 1212
 MNER 3 No Error Check should check @@error after update 1216
 MNER 3 No Error Check should check return value of exec 1218
 MNER 3 No Error Check should check @@error after delete 1223
 MNER 3 No Error Check should check @@error after delete 1224
 MUCO 3 Useless Code Useless Brackets 116
 MUCO 3 Useless Code Useless Brackets 117
 MUCO 3 Useless Code Useless Brackets 118
 MUCO 3 Useless Code Useless Brackets 119
 MUCO 3 Useless Code Useless Brackets 182
 MUCO 3 Useless Code Useless Brackets 202
 MUCO 3 Useless Code Useless Brackets 228
 MUCO 3 Useless Code Useless Brackets 235
 MUCO 3 Useless Code Useless Brackets 266
 MUCO 3 Useless Code Useless Brackets 294
 MUCO 3 Useless Code Useless Brackets 295
 MUCO 3 Useless Code Useless Brackets 296
 MUCO 3 Useless Code Useless Brackets 297
 MUCO 3 Useless Code Useless Brackets 300
 MUCO 3 Useless Code Useless Brackets 305
 MUCO 3 Useless Code Useless Brackets 306
 MUCO 3 Useless Code Useless Brackets 307
 MUCO 3 Useless Code Useless Brackets 346
 MUCO 3 Useless Code Useless Begin-End Pair 426
 MUCO 3 Useless Code Useless Brackets 456
 MUCO 3 Useless Code Useless Brackets 460
 MUCO 3 Useless Code Useless Brackets 472
 MUCO 3 Useless Code Useless Brackets 474
 MUCO 3 Useless Code Useless Brackets 479
 MUCO 3 Useless Code Useless Brackets 643
 MUCO 3 Useless Code Useless Brackets 670
 MUCO 3 Useless Code Useless Brackets 729
 MUCO 3 Useless Code Useless Brackets 754
 MUCO 3 Useless Code Useless Brackets 759
 MUCO 3 Useless Code Useless Brackets 764
 MUCO 3 Useless Code Useless Brackets 946
 MUCO 3 Useless Code Useless Brackets 948
 MUCO 3 Useless Code Useless Brackets 965
 MUCO 3 Useless Code Useless Brackets 978
 MUCO 3 Useless Code Useless Brackets 1002
 MUCO 3 Useless Code Useless Brackets 1021
 MUCO 3 Useless Code Useless Brackets 1026
 MUCO 3 Useless Code Useless Brackets 1044
 MUCO 3 Useless Code Useless Brackets 1051
 MUCO 3 Useless Code Useless Brackets 1081
 MUCO 3 Useless Code Useless Brackets 1157
 MUCO 3 Useless Code Useless Brackets 1230
 MUIN 3 Column created using implicit nullability 690
 MUIN 3 Column created using implicit nullability 1071
 MUIN 3 Column created using implicit nullability 1164
 MUIN 3 Column created using implicit nullability 1165
 MUTI 3 Update temptable with identity - 12.5.4 Regression 1212
 MUTI 3 Update temptable with identity - 12.5.4 Regression 1216
 QAFM 3 Var Assignment from potentially many rows 348
 QAFM 3 Var Assignment from potentially many rows 352
 QAFM 3 Var Assignment from potentially many rows 654
 QAFM 3 Var Assignment from potentially many rows 738
 QAFM 3 Var Assignment from potentially many rows 785
 QAFM 3 Var Assignment from potentially many rows 803
 QAFM 3 Var Assignment from potentially many rows 1064
 QAFM 3 Var Assignment from potentially many rows 1124
 QCRS 3 Conditional Result Set 997
 QCRS 3 Conditional Result Set 1036
 QCTC 3 Conditional Table Creation 114
 QCTC 3 Conditional Table Creation 148
 QCTC 3 Conditional Table Creation 167
 QCTC 3 Conditional Table Creation 245
 QCTC 3 Conditional Table Creation 690
 QCTC 3 Conditional Table Creation 852
 QCTC 3 Conditional Table Creation 1071
 QCTC 3 Conditional Table Creation 1164
 QCTC 3 Conditional Table Creation 1165
 QGWO 3 Group by/Distinct/Union without order by 1036
 QISO 3 Set isolation level 84
 QIWC 3 Insert with not all columns specified missing 2 columns out of 4 1093
 QJWT 3 Join or Sarg Without Index on temp table 360
 QJWT 3 Join or Sarg Without Index on temp table 507
 QJWT 3 Join or Sarg Without Index on temp table 529
 QJWT 3 Join or Sarg Without Index on temp table 1190
 QJWT 3 Join or Sarg Without Index on temp table 1202
 QNAJ 3 Not using ANSI Inner Join 128
 QNAJ 3 Not using ANSI Inner Join 156
 QNAJ 3 Not using ANSI Inner Join 256
 QNAJ 3 Not using ANSI Inner Join 318
 QNAJ 3 Not using ANSI Inner Join 359
 QNAJ 3 Not using ANSI Inner Join 715
 QNAJ 3 Not using ANSI Inner Join 859
 QNAJ 3 Not using ANSI Inner Join 902
 QNAJ 3 Not using ANSI Inner Join 1188
 QNAJ 3 Not using ANSI Inner Join 1201
 QNAO 3 Not using ANSI Outer Join 170
 QNAO 3 Not using ANSI Outer Join 484
 QNUA 3 Should use Alias: Column uid should use alias o 115
 QNUA 3 Should use Alias: Column uid should use alias o 281
 QNUA 3 Should use Alias: Column Object_status should use alias a 358
 QNUA 3 Should use Alias: Column colid should use alias c 428
 QNUA 3 Should use Alias: Column text should use alias syscomments 1201
 QNUA 3 Should use Alias: Table #helpcpc 1201
 QNUA 3 Should use Alias: Table sybsystemprocs..syscomments 1201
 QNUA 3 Should use Alias: Column computedcol should use alias #helpcpc 1202
 QNUA 3 Should use Alias: Column id should use alias syscomments 1202
 QNUA 3 Should use Alias: Column row_id should use alias #helpcpc 1202
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
91
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
Uncovered: [dlevel]
136
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
Uncovered: [dlevel]
327
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
354
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
374
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {colid, id}
Uncovered: [number]
466
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
485
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
603
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, attribute, object_cinfo}
655
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class}
Uncovered: [attribute, object_info1, object_info2, object_info3, object_cinfo]
721
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, attribute}
Uncovered: [object_info1, object_info2, object_info3, object_cinfo]
722
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {id}
739
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class}
Uncovered: [attribute, object_info1, object_info2, object_info3, object_cinfo]
865
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, attribute}
Uncovered: [object_info1, object_info2, object_info3, object_cinfo]
866
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: systabstats.csystabstats unique clustered
(id, indid, partitionid)
Intersection: {indid, id}
1041
 QSWV 3 Sarg with variable @valstat3, Candidate Index: spt_values.spt_valuesclust clustered(number, type) F 350
 QSWV 3 Sarg with variable @valstat3, Candidate Index: spt_values.spt_valuesclust clustered(number, type) F 367
 QTLO 3 Top-Level OR 501
 QTLO 3 Top-Level OR 580
 QTLO 3 Top-Level OR 586
 VNRD 3 Variable is not read @dbname 651
 VNRD 3 Variable is not read @retcode 651
 VNRD 3 Variable is not read @site 651
 VNRD 3 Variable is not read @owner 652
 VUNU 3 Variable is not used @len1 45
 VUNU 3 Variable is not used @len2 45
 VUNU 3 Variable is not used @len3 45
 VUNU 3 Variable is not used @len4 45
 VUNU 3 Variable is not used @len5 45
 VUNU 3 Variable is not used @len6 45
 MRST 2 Result Set Marker 997
 MRST 2 Result Set Marker 1036
 MSUB 2 Subquery Marker 90
 MSUB 2 Subquery Marker 209
 MSUB 2 Subquery Marker 235
 MSUB 2 Subquery Marker 372
 MSUB 2 Subquery Marker 507
 MSUB 2 Subquery Marker 523
 MSUB 2 Subquery Marker 529
 MSUB 2 Subquery Marker 539
 MSUB 2 Subquery Marker 549
 MSUB 2 Subquery Marker 560
 MSUB 2 Subquery Marker 570
 MSUB 2 Subquery Marker 580
 MSUB 2 Subquery Marker 603
 MSUB 2 Subquery Marker 729
 MSUB 2 Subquery Marker 841
 MSUB 2 Subquery Marker 902
 MSUB 2 Subquery Marker 1133
 MSUB 2 Subquery Marker 1188
 MSUC 2 Correlated Subquery Marker 462
 MTR1 2 Metrics: Comments Ratio Comments: 30% 34
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 141 = 147dec - 8exi + 2 34
 MTR3 2 Metrics: Query Complexity Complexity: 635 34
 PRED_QUERY_COLLECTION 2 {m=master..sysmessages, o=sybsystemprocs..sysobjects, sv=master..spt_values, sv2=master..spt_values} 0 114
 PRED_QUERY_COLLECTION 2 {t=sybsystemprocs..systypes, t2=sybsystemprocs..systypes} 0 148
 PRED_QUERY_COLLECTION 2 {t=sybsystemprocs..systypes, t2=sybsystemprocs..systypes} 0 245
 PRED_QUERY_COLLECTION 2 {m=master..sysmessages, o=sybsystemprocs..sysobjects, sv=master..spt_values, sv2=master..spt_values} 0 280
 PRED_QUERY_COLLECTION 2 {a=sybsystemprocs..sysattributes, c=sybsystemprocs..syscolumns} 0 462
 PRED_QUERY_COLLECTION 2 {a=master..sysattributes, a2=sybsystemprocs..sysattributes, a3=master..sysattributes} 0 710
 PRED_QUERY_COLLECTION 2 {a=sybsystemprocs..sysattributes, a2=master..sysattributes, a3=master..sysattributes} 0 852
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, o2=sybsystemprocs..sysobjects} 0 902

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_namecrack  
calls proc sybsystemprocs..sp_getmessage  
   reads table sybsystemprocs..sysusermessages  
   reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
writes table tempdb..#sphelp1rs (1) 
reads table sybsystemprocs..sysattributes  
reads table master..sysattributes (1)  
reads table master..sysmessages (1)  
writes table tempdb..#sphelp3rs (1) 
calls proc sybsystemprocs..sp_helpcomputedcolumn  
   read_writes table tempdb..#helpcpc (1) 
   calls proc sybsystemprocs..sp_autoformat  
      calls proc sybsystemprocs..sp_namecrack  
      read_writes table tempdb..#colinfo_af (1) 
      reads table tempdb..systypes (1)  
      reads table tempdb..syscolumns (1)  
      calls proc sybsystemprocs..sp_autoformat  
      reads table master..syscolumns (1)  
      reads table master..systypes (1)  
   reads table sybsystemprocs..syscomments  
   reads table master..syscurconfigs (1)  
   read_writes table tempdb..#helptxt (1) 
   writes table tempdb..#helpname (1) 
   reads table sybsystemprocs..sysobjects  
   reads table sybsystemprocs..syscolumns  
   calls proc sybsystemprocs..sp_getmessage  
read_writes table tempdb..#helptype (1) 
writes table tempdb..#sphelp4rs (1) 
calls proc sybsystemprocs..sp_helpkey  
   calls proc sybsystemprocs..sp_autoformat  
   writes table tempdb..#sphelpkeys2rs (1) 
   reads table master..spt_values (1)  
   reads table sybsystemprocs..sysobjects  
   reads table sybsystemprocs..syskeys  
   writes table tempdb..#sphelpkeys1rs (1) 
   calls proc sybsystemprocs..sp_getmessage  
reads table sybsystemprocs..sysxtypes  
reads table sybsystemprocs..syscolumns  
reads table sybsystemprocs..syscomments  
reads table master..syscurconfigs (1)  
calls proc sybsystemprocs..sp_helpindex  
   reads table sybsystemprocs..syssegments  
   calls proc sybsystemprocs..sp_helpcomputedcolumn  
   calls proc sybsystemprocs..sp_autoformat  
   reads table sybsystemprocs..sysindexes  
   reads table master..spt_values (1)  
   reads table master..sysattributes (1)  
   calls proc sybsystemprocs..sp_getmessage  
   reads table sybsystemprocs..sysattributes  
   writes table tempdb..#sphelpindex1rs (1) 
   read_writes table tempdb..#spindattr (1) 
   writes table tempdb..#spindptn (1) 
   writes table tempdb..#spindtab (1) 
   reads table sybsystemprocs..sysobjects  
   reads table sybsystemprocs..syspartitions  
writes table tempdb..#sphelp2rs (1) 
reads table sybsystemprocs..systabstats  
calls proc sybsystemprocs..sp_autoformat  
read_writes table tempdb..#helptxt (1) 
writes table tempdb..#sphelp5rs (1) 
reads table sybsystemprocs..sysjars  
read_writes table tempdb..#sphelpattr (1) 
reads table sybsystemprocs..sysobjects  
reads table sybsystemprocs..systypes  
reads table sybsystemprocs..sysindexes  
reads table master..spt_values (1)  
calls proc sybsystemprocs..sp_helpartition  
   reads table sybsystemprocs..syspartitions  
   reads table sybsystemprocs..syssegments  
   reads table sybsystemprocs..syscolumns  
   writes table tempdb..#result_all (1) 
   read_writes table tempdb..#col_names (1) 
   reads table sybsystemprocs..sysindexes  
   reads table sybsystemprocs..syscomments  
   reads table sybsystemprocs..sysobjects  
   reads table master..spt_values (1)  
   writes table tempdb..#result_body (1) 
   calls proc sybsystemprocs..sp_autoformat  
   calls proc sybsystemprocs..sp_getmessage  
   writes table tempdb..#all_tables (1) 
   reads table sybsystemprocs..syspartitionkeys  
   writes table tempdb..#result_cond (1) 
   reads table master..syscurconfigs (1)  
   writes table tempdb..#result_head (1) 
read_writes table tempdb..#helpcpc (1) 
writes table tempdb..#sphelp6rs (1) 
calls proc sybsystemprocs..sp_help_params  
   calls proc sybsystemprocs..sp_autoformat  
   reads table sybsystemprocs..syscolumns  
   reads table master..spt_values (1)  
   reads table sybsystemprocs..systypes  
   reads table master..sysmessages (1)  
   reads table sybsystemprocs..sysxtypes  
   read_writes table tempdb..#helpproc (1) 
writes table tempdb..#helpname (1)