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

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     /*	4.8	1.1	06/14/90	sproc/src/help */
4     /*
5     ** Messages for "sp_helpindex"          17640
6     **
7     ** 17460, "Object must be in the current database."
8     ** 17461, "Object does not exist in this database."
9     ** 17640, "Object does not have any indexes."
10    ** 17641, "Object has the following indexes"
11    */
12    
13    /*
14    ** IMPORTANT NOTE:
15    ** This stored procedure uses the built-in function object_id() in the
16    ** where clause of a select query. If you intend to change this query
17    ** or use the object_id() or db_id() builtin in this procedure, please read the
18    ** READ.ME file in the $DBMS/generic/sproc directory to ensure that the rules
19    ** pertaining to object-id's and db-id's outlined there, are followed.
20    */
21    
22    create procedure sp_helpindex
23        @objname varchar(767) /* the table to check for indexes */
24    as
25    
26        declare @indid int /* the index id of an index */
27        declare @keys varchar(1024) /* string to build up index key in */
28        declare @inddesc varchar(68) /* string to build up index desc in */
29        declare @msg varchar(1024)
30    
31        if @@trancount = 0
32        begin
33            set chained off
34        end
35    
36        set transaction isolation level 1
37    
38        /*
39        **  Check to see that the object names are local to the current database.
40        */
41        if @objname like "%.%.%" and
42            substring(@objname, 1, charindex(".", @objname) - 1) != db_name()
43        begin
44            /* 17460, "Object must be in the current database." */
45            raiserror 17460
46            return (1)
47        end
48    
49        /*
50        **  Check to see the the table exists 
51        */
52        if not exists (select id
53                from sysobjects
54                where id = object_id(@objname))
55        /*
56        **  Table doesn't exist so return.
57        */
58        begin
59            /* 17461, "Object does not exist in this database." */
60            raiserror 17461
61            return (1)
62        end
63    
64        /*
65        **  See if the object has any indexes.
66        **  Since there may be more than one entry in sysindexes for the object,
67        **  this select will set @indid to the index id of the first index.
68        */
69        select @indid = min(indid)
70        from sysindexes
71        where id = object_id(@objname)
72            and indid > 0
73            and indid < 255
74    
75        /*
76        **  If no indexes, return.
77        */
78        if @indid is NULL
79        begin
80            /* 17640, "Object does not have any indexes." */
81            exec sp_getmessage 17640, @msg output
82            print @msg
83            return (1)
84        end
85    
86        /*
87        **  Now check out each index, figure out it's type and keys and
88        **  save the info in a temporary table that we'll print out at the end.
89        */
90        create table #spindtab
91        (
92            index_name varchar(255),
93            index_keys varchar(1024),
94            index_description varchar(68),
95            index_max_rows_per_page smallint,
96            index_fillfactor smallint,
97            index_reservepagegap smallint,
98            index_created datetime NULL,
99            index_local varchar(255)
100       )
101   
102       /*  Create temporary table for sysattributes data */
103       create table #spindattr
104       (
105           name varchar(255),
106           class smallint,
107           attribute smallint,
108           int_value int NULL,
109           char_value varchar(255) NULL,
110           comments varchar(255) NULL
111       )
112   
113       /* This is for the index partition information. */
114       create table #spindptn
115       (
116           index_ptn_name varchar(255),
117           index_ptn_seg varchar(255),
118       )
119   
120       while @indid is not NULL
121       begin
122   
123           /*
124           **  First we'll figure out what the keys are.
125           */
126           declare @i int
127           declare @thiskey varchar(255)
128           declare @sorder char(4)
129           declare @lastindid int
130   
131           select @keys = "", @i = 1
132   
133           set nocount on
134   
135           while @i <= 31
136           begin
137               select @thiskey = index_col(@objname, @indid, @i)
138   
139               if (@thiskey is NULL)
140               begin
141                   goto keysdone
142               end
143   
144               if @i > 1
145               begin
146                   select @keys = @keys + ", "
147               end
148   
149               /*select @keys = @keys + index_col(@objname, @indid, @i)*/
150               select @keys = @keys + @thiskey
151   
152               /*
153               ** Get the sort order of the column using index_colorder()
154               ** This support is added for handling descending keys.
155               */
156               select @sorder = index_colorder(@objname, @indid, @i)
157               if (@sorder = "DESC")
158                   select @keys = @keys + " " + @sorder
159   
160               /*
161               **  Increment @i so it will check for the next key.
162               */
163               select @i = @i + 1
164   
165           end
166   
167   
168           /*
169           **  When we get here we now have all the keys.
170           */
171   keysdone:
172           set nocount off
173   
174   
175           /*
176           **  Initialize the index description by figuring out if it's a
177           **  clustered or nonclustered index.
178           */
179           if @indid = 1
180           begin
181               select @inddesc = "clustered"
182           end
183           if @indid > 1
184           begin
185               if exists (select * from sysindexes i
186                       where status2 & 512 = 512
187                           and i.indid = @indid
188                           and i.id = object_id(@objname))
189               begin
190                   select @inddesc = "clustered"
191               end
192               else
193               begin
194                   select @inddesc = "nonclustered"
195               end
196           end
197   
198           /*
199           **  Now we'll check out the status bits for this index and
200           **  build an english description from them.
201           */
202   
203           /*
204           **  See if the index is unique (0x02).
205           */
206           if exists (select *
207                   from master.dbo.spt_values v, sysindexes i
208                   where i.status & v.number = v.number
209                       and v.type = "I"
210                       and v.number = 2
211                       and i.id = object_id(@objname)
212                       and i.indid = @indid)
213           begin
214               select @inddesc = @inddesc + ", " + v.name
215               from master.dbo.spt_values v, sysindexes i
216               where i.status & v.number = v.number
217                   and v.type = "I"
218                   and v.number = 2
219                   and i.id = object_id(@objname)
220                   and i.indid = @indid
221           end
222           else
223           /* 
224           ** if this is a nonunique clustered index on dol tables, dup rows
225           ** are allowed
226           */
227           if exists (select * from sysindexes i
228                   where status2 & 512 = 512
229                       and i.indid = @indid
230                       and i.id = object_id(@objname))
231           begin
232               select @inddesc = @inddesc + ", " + v.name
233               from master.dbo.spt_values v, sysindexes i
234               where v.type = "I"
235                   and v.number = 64
236                   and i.id = object_id(@objname)
237                   and i.indid = @indid
238           end
239   
240           /*
241           **  See if the index is ignore_dupkey (0x01).
242           */
243           if exists (select *
244                   from master.dbo.spt_values v, sysindexes i
245                   where i.status & v.number = v.number
246                       and v.type = "I"
247                       and v.number = 1
248                       and i.id = object_id(@objname)
249                       and i.indid = @indid)
250           begin
251               select @inddesc = @inddesc + ", " + v.name
252               from master.dbo.spt_values v, sysindexes i
253               where i.status & v.number = v.number
254                   and v.type = "I"
255                   and v.number = 1
256                   and i.id = object_id(@objname)
257                   and i.indid = @indid
258           end
259   
260           /*
261           **  See if the index is ignore_dup_row (0x04).
262           */
263           if exists (select *
264                   from master.dbo.spt_values v, sysindexes i
265                   where i.status & v.number = v.number
266                       and v.type = "I"
267                       and v.number = 4
268                       and i.id = object_id(@objname)
269                       and i.indid = @indid)
270           begin
271               select @inddesc = @inddesc + ", " + v.name
272               from master.dbo.spt_values v, sysindexes i
273               where i.status & v.number = v.number
274                   and v.type = "I"
275                   and v.number = 4
276                   and i.id = object_id(@objname)
277                   and i.indid = @indid
278           end
279   
280           /*
281           **  See if the index is allow_dup_row (0x40).
282           */
283           if exists (select *
284                   from master.dbo.spt_values v, sysindexes i
285                   where i.status & v.number = v.number
286                       and v.type = "I"
287                       and v.number = 64
288                       and i.id = object_id(@objname)
289                       and i.indid = @indid)
290           begin
291               select @inddesc = @inddesc + ", " + v.name
292               from master.dbo.spt_values v, sysindexes i
293               where i.status & v.number = v.number
294                   and v.type = "I"
295                   and v.number = 64
296                   and i.id = object_id(@objname)
297                   and i.indid = @indid
298           end
299   
300           /*
301           **  Now we have the whole description for the index so we'll add
302           **  the goods to the temporary table.
303           */
304           insert into #spindtab
305           select name, @keys, @inddesc, maxrowsperpage, fill_factor,
306               isnull(res_page_gap, 0), crdate,
307               case when (status3 & 8 = 8) then "Local Index"
308                   else "Global Index"
309               end
310           from sysindexes
311           where id = object_id(@objname)
312               and indid = @indid
313           /* 
314           **  Get sysattributes data if there is any
315           */
316           insert #spindattr(name, class, attribute, int_value, char_value,
317               comments)
318           select i.name, a.class, a.attribute, a.int_value, a.char_value,
319               a.comments
320           from sysindexes i, sysattributes a
321           where a.object_type = "I"
322               and a.object = object_id(@objname)
323               and a.object_info1 = @indid
324               and i.id = object_id(@objname)
325               and i.indid = @indid
326   
327   
328           insert #spindptn(index_ptn_name, index_ptn_seg)
329           select idxptn_nam = p.name, segment = s.name
330           from syssegments s, syspartitions p
331           where s.segment = p.segment and p.id = object_id(@objname)
332               and p.indid = @indid
333   
334           /*
335           ** It's possible a cache is deleted without doing an unbind first. After
336           ** a server reboot the binding is marked 'invalid' (int_value = 0).
337           ** If we have such an invalid binding, don't show it in the output.
338           */
339           delete from #spindattr
340           where class = 3
341               and attribute = 0
342               and int_value = 0
343   
344           /*
345           **  Now move @indid to the next index.
346           */
347           select @lastindid = @indid
348           select @indid = NULL
349           select @indid = min(indid)
350           from sysindexes
351           where id = object_id(@objname)
352               and indid > @lastindid
353               and indid < 255
354       end
355   
356       /*
357       **  Now print out the contents of the temporary index table.
358       */
359       exec sp_getmessage 17641, @msg output
360       print @msg
361       print ""
362   
363       exec sp_autoformat #spindtab
364   
365       drop table #spindtab
366   
367       exec sp_autoformat #spindptn
368   
369       drop table #spindptn
370   
371   
372       /*
373       ** If the table has functional indexes, display all the functional index 
374       ** keys.
375       */
376       if exists (select 1 from sysindexes i where i.id = object_id(@objname) and
377                   (status2 & 32768) = 32768)
378       begin
379           print ""
380           execute dbo.sp_helpcomputedcolumn @objname, 1
381       end
382   
383       /*  
384       ** Print sysattributes data if there is any.  The join with
385       ** master..sysattributes is to get the string descriptions for the
386       ** class (master.dbo.sysattributes cn) and attribute
387       ** (master.dbo.sysattributes an). These should never be more than
388       ** 30 characters, so it's okay to truncate them to 30.
389       */
390   
391       if exists (select * from #spindattr)
392       begin
393           select name = a.name, attribute_class =
394               convert(varchar(448), cn.char_value),
395               attribute = convert(varchar(448), an.char_value),
396               a.int_value,
397               char_value = convert(varchar(448), a.char_value), a.comments
398           into #sphelpindex1rs
399           from #spindattr a, master.dbo.sysattributes cn,
400               master.dbo.sysattributes an
401           where a.class = cn.object
402               and a.attribute = an.object_info1
403               and a.class = an.object
404               and cn.class = 0
405               and cn.attribute = 0
406               and an.class = 0
407               and an.attribute = 1
408           exec sp_autoformat @fulltabname = #sphelpindex1rs
409           drop table #sphelpindex1rs
410       end
411       drop table #spindattr
412       return (0)
413



exec sp_procxmode 'sp_helpindex', 'AnyMode'
go

Grant Execute on sp_helpindex to public
go

DEFECTS
QJWI 5 Join or Sarg Without Index	208
QJWI 5 Join or Sarg Without Index	216
QJWI 5 Join or Sarg Without Index	245
QJWI 5 Join or Sarg Without Index	253
QJWI 5 Join or Sarg Without Index	265
QJWI 5 Join or Sarg Without Index	273
QJWI 5 Join or Sarg Without Index	285
QJWI 5 Join or Sarg Without Index	293
QJWI 5 Join or Sarg Without Index	331
MEST 4 Empty String will be replaced by Single Space	131
MEST 4 Empty String will be replaced by Single Space	361
MEST 4 Empty String will be replaced by Single Space	379
MINU 4 Unique Index with nullable columns master..sysattributes	master..sysattributes
MINU 4 Unique Index with nullable columns sybsystemprocs..sysattributes	sybsystemprocs..sysattributes
MMCN 4 Potentially Misleading Column Naming	329
MTYP 4 Assignment type mismatch char_value: varchar(255) = varchar(768)	318
MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1	363
MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1	367
MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname	408
QCAS 4 Cartesian product with single row between tables master..spt_values v and [sybsystemprocs..sysindexes i], 2 tables with rc=1	232
QCAS 4 Cartesian product with single row between tables sybsystemprocs..sysindexes i and [sybsystemprocs..sysattributes a], 2 tables with rc=...	318
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, object_info1, object_type}	321
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	72
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	73
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	187
QTYP 4 Comparison type mismatch smallint = int	187
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	212
QTYP 4 Comparison type mismatch smallint = int	212
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	220
QTYP 4 Comparison type mismatch smallint = int	220
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	229
QTYP 4 Comparison type mismatch smallint = int	229
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	237
QTYP 4 Comparison type mismatch smallint = int	237
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	249
QTYP 4 Comparison type mismatch smallint = int	249
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	269
QTYP 4 Comparison type mismatch smallint = int	269
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	277
QTYP 4 Comparison type mismatch smallint = int	277
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	289
QTYP 4 Comparison type mismatch smallint = int	289
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	297
QTYP 4 Comparison type mismatch smallint = int	297
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	312
QTYP 4 Comparison type mismatch smallint = int	312
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	325
QTYP 4 Comparison type mismatch smallint = int	325
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	332
QTYP 4 Comparison type mismatch smallint = int	332
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	340
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	341
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	352
QTYP 4 Comparison type mismatch smallint = int	352
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	353
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	401
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	402
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	403
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	404
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	405
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	406
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	407
TNOI 4 Table with no index sybsystemprocs..syssegments	sybsystemprocs..syssegments
TNOU 4 Table with no unique index master..spt_values	master..spt_values
MGTP 3 Grant to public master..spt_values
MGTP 3 Grant to public master..sysattributes
MGTP 3 Grant to public sybsystemprocs..sp_helpindex
MGTP 3 Grant to public sybsystemprocs..sysattributes
MGTP 3 Grant to public sybsystemprocs..sysindexes
MGTP 3 Grant to public sybsystemprocs..sysobjects
MGTP 3 Grant to public sybsystemprocs..syspartitions
MGTP 3 Grant to public sybsystemprocs..syssegments
MNER 3 No Error Check should check return value of exec	81
MNER 3 No Error Check should check @@error after insert	304
MNER 3 No Error Check should check @@error after insert	316
MNER 3 No Error Check should check @@error after insert	328
MNER 3 No Error Check should check @@error after delete	339
MNER 3 No Error Check should check return value of exec	359
MNER 3 No Error Check should check return value of exec	363
MNER 3 No Error Check should check return value of exec	367
MNER 3 No Error Check should check return value of exec	380
MNER 3 No Error Check should check @@error after select into	393
MNER 3 No Error Check should check return value of exec	408
MUCO 3 Useless Code Useless Brackets	46
MUCO 3 Useless Code Useless Brackets	61
MUCO 3 Useless Code Useless Brackets	83
MUCO 3 Useless Code Useless Brackets	139
MUCO 3 Useless Code Useless Brackets	157
MUCO 3 Useless Code Useless Brackets	307
MUCO 3 Useless Code Useless Brackets	412
MUIN 3 Column created using implicit nullability	90
MUIN 3 Column created using implicit nullability	103
MUIN 3 Column created using implicit nullability	114
QCTC 3 Conditional Table Creation	393
QISO 3 Set isolation level	36
QJWT 3 Join or Sarg Without Index on temp table	401
QJWT 3 Join or Sarg Without Index on temp table	402
QNAJ 3 Not using ANSI Inner Join	207
QNAJ 3 Not using ANSI Inner Join	215
QNAJ 3 Not using ANSI Inner Join	233
QNAJ 3 Not using ANSI Inner Join	244
QNAJ 3 Not using ANSI Inner Join	252
QNAJ 3 Not using ANSI Inner Join	264
QNAJ 3 Not using ANSI Inner Join	272
QNAJ 3 Not using ANSI Inner Join	284
QNAJ 3 Not using ANSI Inner Join	292
QNAJ 3 Not using ANSI Inner Join	320
QNAJ 3 Not using ANSI Inner Join	330
QNAJ 3 Not using ANSI Inner Join	399
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syspartitions.csyspartitions unique clustered (id, indid, partitionid) Intersection: {indid, id}	331
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered (id, indid) Intersection: {id}	376
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}	404
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}	406
MSUB 2 Subquery Marker	52
MSUB 2 Subquery Marker	185
MSUB 2 Subquery Marker	206
MSUB 2 Subquery Marker	227
MSUB 2 Subquery Marker	243
MSUB 2 Subquery Marker	263
MSUB 2 Subquery Marker	283
MSUB 2 Subquery Marker	376
MTR1 2 Metrics: Comments Ratio Comments: 33%	22
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 68 = 70dec - 4exi + 2	22
MTR3 2 Metrics: Query Complexity Complexity: 222	22
PRED_QUERY_COLLECTION 2 {p=sybsystemprocs..syspartitions, s=sybsystemprocs..syssegments} 0	329