DatabaseProcApplicationCreatedLinks
sybsystemprocssp_helpartition  31 Aug 14Defects Dependencies

1     
2     /*
3     ** Messages for "sp_helpartition"          
4     **
5     ** 17460, "Object must be in the current database."
6     ** 17733, "There is no table named '%1!'."
7     ** 17734, "There is no index named '%1!' for table '%2!'."
8     ** 19305, "There is no partition named '%1!' for table '%2!', index '%3!'."
9     ** 19337, "Text is encrypted"
10    ** 19340, "Warning: Configuration Parameter 'allow select on syscomments.text' 
11    **	   is set to 0. Only the object owner or user with sa_role can access 
12    **	   the text. The text for the partition condition cannot be displayed."
13    */
14    
15    /*
16    ** IMPORTANT NOTE:
17    ** This stored procedure uses the built-in function object_id() in the
18    ** where clause of a select query. If you intend to change this query
19    ** or use the object_id() or db_id() builtin in this procedure, please read the
20    ** READ.ME file in the $DBMS/generic/sproc directory to ensure that the rules
21    ** pertaining to object-id's and db-id's outlined there, are followed.
22    **
23    ** Parameters:
24    **	@tabname	- if null, the number of partitions for all
25    **			  user tables are reported
26    **	@indexname	- if null, the partition info of the base table
27    **			  is reported
28    **			- if 'all', the partition info of all indexes,
29    **			  including the base table(if there is no clustered
30    **			  index on the table), will be reported, but no
31    **			  partition level detailed information.
32    **			- else, the partition info of that particular index
33    **			  including all partition level details will be
34    **			  reported.
35    **	@partitionname	- if null, the partition info of that particular index
36    **			  including all partition level details will be
37    **			  reported.
38    **			- if specified, the partition info of that particular 
39    **			  index and the partition level detail of that 
40    **			  particular partition is reported.
41    */
42    create procedure sp_helpartition
43        @tabname varchar(767) = NULL, /* the table to check for partitions */
44        @indexname varchar(255) = NULL,
45        @partitionname varchar(255) = NULL
46    as
47    
48        declare @tableid int,
49            @indexid int,
50            @dbid int,
51            @use_savedname int,
52            @columnname varchar(255),
53            @columnlist varchar(1024),
54            @indid smallint,
55            @current_indid smallint,
56            @indextype varchar(16),
57            @datapage_indid smallint,
58            @ptntype smallint,
59            @typename varchar(12),
60            @condid int,
61            @range smallint,
62            @hash smallint,
63            @roundrobin smallint,
64            @list smallint,
65            @config_parm int,
66            @hiddenmsg varchar(255),
67            @disallowmsg varchar(255),
68            @ptnid int,
69            @tablevel int,
70            @ptnlevel int,
71            @avg_pages int,
72            @max_pages int,
73            @min_pages int,
74            @datapage_ptnid int
75    
76        if @@trancount = 0
77        begin
78            set transaction isolation level 1
79            set chained off
80        end
81    
82        /* 
83        ** Since different partition types have different information content,
84        ** we need to find the partition types first.
85        */
86    
87        select @range = v.number from master.dbo.spt_values v
88        where v.type = 'PN' and v.name = 'range'
89        select @hash = v.number from master.dbo.spt_values v
90        where v.type = 'PN' and v.name = 'hash'
91        select @roundrobin = v.number from master.dbo.spt_values v
92        where v.type = 'PN' and v.name = 'roundrobin'
93        select @list = v.number from master.dbo.spt_values v
94        where v.type = 'PN' and v.name = 'list'
95    
96        set nocount on
97    
98        /*
99        **  If no object name is specified, print all user tables in the database 
100       */
101       if @tabname is null
102       begin
103   
104           select "owner" = user_name(uid), o.name
105               , "partitions" = (select count(*)
106                   from syspartitions p
107                   where p.id = o.id
108                       and p.indid < 2)
109               , "partition_type" = (select v.name
110                   from master.dbo.spt_values v
111                   where v.type = 'PN'
112                       and v.number = isnull(i.partitiontype, @roundrobin))
113           into #all_tables
114           from sysobjects o, sysindexes i
115           where o.type = "U" and o.id = i.id and i.indid < 2
116   
117           exec sp_autoformat @fulltabname = "#all_tables", @orderby = "order by 1, 2"
118           -- order by 1,2
119   
120           return 0
121   
122       end
123   
124       /*
125       **  Check to see that the object names are local to the current database.
126       */
127       if @tabname like '%.%.%' and
128           substring(@tabname, 1, charindex('.', @tabname) - 1) != db_name()
129       begin
130           /* 17460, "Object must be in the current database." */
131           raiserror 17460
132           return (1)
133       end
134   
135       /*
136       **  Check to see that the table exists
137       */
138       if not exists (select 1 from sysobjects
139               where id = object_id(@tabname)
140                   and (type = 'S' or type = 'U'))
141       begin
142           /** 17733, "There is no table named '%1!'."*/
143           raiserror 17733, @tabname
144           return (1)
145       end
146   
147       select @tableid = object_id(@tabname)
148           , @dbid = db_id()
149   
150       /* 
151       ** Check validity of tablename with index name if provided
152       */
153       if @indexname is not null and @indexname != 'all' and
154           not exists (select 1
155               from sysindexes
156               where id = @tableid
157                   and name = @indexname)
158       begin
159           /* 17734, "There is no index named '%1!' for table '%2!'."*/
160           raiserror 17734, @indexname, @tabname
161           return (1)
162       end
163   
164       /*
165       ** If partion name is provided make sure the name is a valid
166       ** partition on the given index, or on the table if the
167       ** index is null
168       */
169       if @indexname != 'all'
170       begin
171           select @indexid = indid
172           from sysindexes
173           where id = @tableid
174               and ((@indexname is null and indid < 2)
175                   or name = @indexname)
176   
177           /*
178           ** If user didn't specify index name, it implicitly
179           ** means the base table, but for table with clustered
180           ** index, the partition name for base table is moved
181           ** to savedname in syspartitions. In this case, we need
182           ** to check the savedname to see if the partition name
183           ** given is valid.
184           */
185           if @indexname is null and @indexid = 1
186               select @use_savedname = 1
187           else
188               select @use_savedname = 0
189   
190           if @partitionname is not null and
191               not exists (select 1
192                   from syspartitions
193                   where id = @tableid
194                       and indid = @indexid
195                       and ((@use_savedname = 0
196                               and name = @partitionname)
197                           or (@use_savedname = 1
198                               and cdataptnname = @partitionname)))
199           /*
200           **  Partition doesn't exist on this index so return.
201           */
202           begin
203               /*
204               ** 19305, "There is no partition named '%1!' for table '%2!', 
205               ** index '%3!'."
206               */
207               if @indexname is null
208                   raiserror 19305, @partitionname, @tabname, @tabname
209               else
210                   raiserror 19305, @partitionname, @tabname, @indexname
211               return (1)
212           end
213       end
214       /*
215       ** If table name is given then we want to create a list of
216       ** column names that represent the partionkeys. Here
217       ** we create a temp table that holds the id, indid, and
218       ** the colname list of the partition keys in the form
219       ** (col1, col2, col3, ...). We can then join this to the
220       ** output below.
221       */
222   
223       declare keycursor cursor for
224       select distinct c.name, k.indid
225       from syspartitionkeys k, syscolumns c
226       where k.id = @tableid
227           and k.id = c.id
228           and c.colid = k.colid
229       order by k.indid, k.position
230       for read only
231   
232       /*
233       ** Create a temp table to hold the partition key list for each table/index.
234       ** There could be maximum of 31 keys, MAXNAME is 30, plus commas between 
235       ** columns, varchar(1024) would be enough to hold the key list.
236       ** Here we specify all columns to be not null and lock allpages to avoid
237       ** renormalization/recompile of this stored procedure when the default
238       ** dboptions change.
239       */
240       create table #col_names(
241           indid smallint not null,
242           col_name_list varchar(1024) null)
243       lock allpages
244   
245       /* We are expecting to have exactly one row for every index in this table. */
246       create unique clustered index col_names_uniqind on #col_names(indid)
247   
248       open keycursor
249   
250   
251       select @columnlist = "", @current_indid = - 1
252   
253       fetch keycursor into @columnname, @indid
254   
255       while (@@sqlstatus = 0)
256       begin
257           /* If first row, set current_indid and columnlist */
258           if (@@rowcount = 1)
259           begin
260               select @columnlist = @columnname
261               select @current_indid = @indid
262           end
263           /* If next row of same index, concate the columnlist */
264           else if (@current_indid = @indid)
265           begin
266               select @columnlist = @columnlist + ', ' + @columnname
267           end
268   
269           /*
270           ** If next index, insert the columnlist for previous index,
271           ** and reset the current_indid and columnlist.
272           */
273           else
274           begin
275               insert #col_names values
276               (@current_indid, @columnlist)
277               select @columnlist = @columnname
278               select @current_indid = @indid
279           end
280   
281           fetch keycursor into @columnname, @indid
282   
283           /*
284           ** If end of cursor, just insert the columnlist for previous
285           ** index, if there had been one.
286           */
287           if (@@sqlstatus = 2)
288           begin
289               insert #col_names values
290               (@current_indid, @columnlist)
291           end
292       end
293   
294       close keycursor
295   
296       deallocate cursor keycursor
297   
298       /*
299       ** Roundrobin partitioned table may have a fake partition
300       ** key if there is a global clustered index on it, need to
301       ** exclude these keys by detecting the partition type is rrobin.
302       */
303       update #col_names set col_name_list = NULL
304       where exists (select 1 from sysindexes i, master.dbo.spt_values v
305               where i.id = @tableid
306                   and i.indid = #col_names.indid
307                   and isnull(i.partitiontype, @roundrobin) = v.number
308                   and v.type = 'PN'
309                   and v.name = 'roundrobin'
310               )
311   
312   
313       /*
314       ** Insert into the #temp table indexes where there is no partition
315       ** columns and the partition columns will be inserted as NULL.
316       */
317       insert #col_names select i.indid, NULL
318       from sysindexes i
319       where id = @tableid and not exists
320               (select 1
321               from syspartitionkeys k
322               where k.id = @tableid and i.indid = k.indid)
323   
324       /*
325       ** Print partition information when 'all' is specified for index 
326       ** When "all" is specified, for each indexes in the table, we
327       ** print the index's name, index type, partition type, partition key
328       ** and number of partitions
329       */
330       if (@indexname = 'all')
331       begin
332           select 'name' = i.name,
333               'type' = case when i.indid = 0 then 'base table'
334                   when i.indid = 255 then 'text/image'
335                   when i.status3 & 8 = 8 then 'local index'
336                   else 'global index'
337               end,
338               'partition_type' = v.name,
339               'partitions' = (select count(*)
340                   from syspartitions p
341                   where p.id = @tableid
342                       and p.indid = i.indid
343   
344                   ),
345               'partition_keys' = n.col_name_list
346           into #result_all lock allpages
347           from sysindexes i, #col_names n, master.dbo.spt_values v
348           where i.id = @tableid
349               and i.indid = n.indid
350               and v.number = isnull(i.partitiontype, @roundrobin)
351               and v.type = 'PN'
352   
353           exec sp_autoformat #result_all
354   
355           return (0)
356       end
357   
358       /*
359       ** When we get here, the indexname is null or specificaly specified,
360       ** print partition information for base table or the specified
361       ** index. According to the partition type of base table or this
362       ** index, we print related information. No matter partition name
363       ** is specified or not, we always print a header information at
364       ** index level, maily the partition type, number of partitions
365       ** and partition keys if applicable. Then, if partition name is
366       ** specified, we print the information of that partition, other
367       ** wise, we will print the information of each partition and a
368       ** summary info of these partitions.
369       */
370   
371       /* Get index type, ptntype, partition key */
372       select @ptntype = isnull(i.partitiontype, @roundrobin),
373           @condid = i.conditionid,
374           @columnlist = n.col_name_list,
375           @indextype =
376           case
377               when i.indid = 0 or @indexname is null
378               then 'base table'
379               when i.indid = 255 then 'text/image'
380               when i.status3 & 8 = 8 then 'local index'
381               else 'global index'
382           end
383       from sysindexes i, #col_names n
384       where i.id = @tableid
385           and i.indid = @indexid
386           and i.indid = n.indid
387   
388       /*
389       ** Get the datapage_indid, which is the index id used in the builtin
390       ** data_pages. For current implementation of this builtin, if user
391       ** want the base table information, we have to passin 0 if the true
392       ** index id is 1 in sysindexes, otherwise, we would get the pages
393       ** only for the index part.
394       */
395       if (@indexname is null)
396           select @datapage_indid = 0
397       else
398           select @datapage_indid = @indexid
399   
400       select @typename = v.name
401       from master.dbo.spt_values v
402       where v.type = 'PN' and v.number = @ptntype
403   
404       /*
405       ** For range and list partition, we will need to display the 
406       ** partition condition text information, check the availability.
407       */
408       select @hiddenmsg = NULL
409       select @disallowmsg = NULL
410   
411       if @ptntype in (@range, @list)
412       begin
413           /*
414           ** If the configuration parameter 'allow select on syscomments.text'
415           ** is set to 0, then the user can access the text ONLY in the
416           ** following cases
417           **
418           **      1. if the user has sa_role
419           **      2. if the object is owned by the user
420           **
421           */
422           select @config_parm = value
423           from master.dbo.syscurconfigs
424           where config = 258
425   
426   
427           if @config_parm = 0 and user_id() != 1
428               and not exists (select name from sysobjects
429                   where uid = user_id()
430                       and id = @tableid)
431           begin
432               exec sp_getmessage 19340, @disallowmsg output
433           end
434   
435           /* See if the partition condition text is hidden */
436           else if exists (select 1 from syscomments c
437                   where c.id = @condid and (c.status & 1 = 1))
438           begin
439               exec sp_getmessage 19337, @hiddenmsg output
440           end
441       end
442   
443       /* Print the header information */
444       select 'name' = case
445               when @indexname is not null then @indexname
446               else @tabname
447           end,
448           'type' = @indextype,
449           'partition_type' = @typename,
450           'partitions' = (select count(*) from syspartitions p
451               where p.id = @tableid
452                   and p.indid = @indexid),
453           'partition_keys' = @columnlist
454       into #result_head lock allpages
455   
456       exec sp_autoformat #result_head
457       print " "
458   
459       /* Get each partition information */
460       create table #result_body(partition_name varchar(255) not null,
461           partition_id int not null,
462           compression_level varchar(20) not null,
463           pages int not null,
464           row_count numeric(18, 0) not null,
465           segment varchar(255) not null,
466           create_date datetime not null,
467           seqnum int not null)
468       lock allpages
469   
470       create table #result_cond(Partition_Conditions varchar(255) null,
471           seqnum int not null,
472           colid int not null,
473           colid2 int not null)
474       lock allpages
475   
476       select @tablevel = isnull(sysstat3, 0)
477       from sysobjects
478       where id = @tableid
479   
480       if (@partitionname is not null)
481       begin
482   
483           select @ptnid = partitionid, @ptnlevel = isnull(status, 0)
484           from syspartitions
485           where id = @tableid
486               and indid = @indexid
487               and ((@use_savedname = 1 and cdataptnname = @partitionname)
488                   or name = @partitionname)
489           insert #result_body
490           select 'partition_name' =
491               case
492                   when @use_savedname = 1 then p.cdataptnname
493                   else p.name
494               end,
495               @ptnid,
496               'compression_level' =
497               case
498                   when (@ptnlevel & 128 > 0) then 'none'
499                   when (@ptnlevel & 32 > 0) then 'page'
500                   when (@ptnlevel & 16 > 0) then 'row'
501                   when (@tablevel & 4096 > 0) then 'inherit from table' /*table row compressed*/
502                   when (@tablevel & 8192 > 0) then 'inherit from table' /*table page compressed*/
503                   else "none"
504               end,
505               data_pages(@dbid, @tableid, @datapage_indid, @ptnid),
506               case
507                   when (@datapage_indid > 1) then 0
508                   else row_count(@dbid, @tableid, @ptnid)
509               end,
510               s.name,
511               p.crdate,
512               0
513           from syspartitions p, syssegments s
514           where p.id = @tableid
515               and p.indid = @indexid
516               and p.segment = s.segment
517               and p.partitionid = @ptnid
518   
519           /* Get partition condition */
520           if @ptntype in (@roundrobin, @hash)
521               insert #result_cond values (NULL, 0, 0, 0)
522           else if (@disallowmsg is null) and (@hiddenmsg is null)
523               insert #result_cond
524               select c.text, c.number, c.colid, c.colid2
525               from syscomments c
526               where c.id = @condid
527                   and c.partitionid = @ptnid
528   
529       end
530   
531       /*
532       ** If we come here, the partition name is not specified,
533       ** we print the information for all partitions under this
534       ** index.
535       ** If range/list/hash partition, print the partitions in
536       ** order their ptncond is stored in syscomments, this order
537       ** is stored in the cnumber field of the syscomments row.
538       */
539       else
540       begin
541           if (@ptntype = @roundrobin)
542           begin
543               insert #result_body
544               select 'partition_name' =
545                   case
546                       when @use_savedname = 1
547                       then p.cdataptnname
548                       else p.name
549                   end,
550                   p.partitionid,
551                   'compression_level' =
552                   case
553                       when (isnull(p.status, 0) & 128 > 0) then 'none'
554                       when (isnull(p.status, 0) & 32 > 0) then 'page'
555                       when (isnull(p.status, 0) & 16 > 0) then 'row'
556                       when (@tablevel & 4096 > 0) then 'inherit from table' /*table row compressed*/
557                       when (@tablevel & 8192 > 0) then 'inherit from table' /*table page compressed*/
558                       else "none"
559                   end,
560                   data_pages(@dbid, @tableid, @datapage_indid,
561                       p.partitionid),
562                   case
563                       when (@datapage_indid > 1) then 0
564                       else row_count(@dbid, @tableid, p.partitionid)
565                   end,
566                       (select s.name from syssegments s
567                       where s.segment = p.segment),
568   
569                   p.crdate,
570   
571                   0
572               from syspartitions p
573               where p.id = @tableid
574                   and p.indid = @indexid
575           end
576           else
577           begin
578               insert #result_body
579               select 'partition_name' =
580                   case
581                       when @use_savedname = 1
582                       then p.cdataptnname
583                       else p.name
584                   end,
585                   p.partitionid,
586                   'compression_level' =
587                   case
588                       when (isnull(p.status, 0) & 128 > 0) then 'none'
589                       when (isnull(p.status, 0) & 32 > 0) then 'page'
590                       when (isnull(p.status, 0) & 16 > 0) then 'row'
591                       when (@tablevel & 4096 > 0) then 'inherit from table' /*table row compressed*/
592                       when (@tablevel & 8192 > 0) then 'inherit from table' /*table page compressed*/
593                       else "none"
594                   end,
595                   data_pages(@dbid, @tableid, @datapage_indid,
596                       p.partitionid),
597                   case
598                       when (@datapage_indid > 1) then 0
599                       else row_count(@dbid, @tableid, p.partitionid)
600                   end,
601                   s.name,
602   
603                   p.crdate,
604   
605                       (select distinct c.number
606                       from syscomments c
607                       where c.id = @condid
608                           and c.partitionid = p.partitionid)
609   
610               from syspartitions p, syssegments s
611               where p.id = @tableid
612                   and p.indid = @indexid
613                   and p.segment = s.segment
614           end
615   
616           /* Get partition condition */
617           if (@ptntype = @roundrobin or @ptntype = @hash)
618               insert #result_cond values (NULL, 0, 0, 0)
619           else if (@disallowmsg is null) and (@hiddenmsg is null)
620               insert #result_cond
621               select c.text, c.number, c.colid, c.colid2
622               from syscomments c
623               where c.id = @condid
624       end
625   
626       if (@indexid > 1)
627       begin --{
628           if (@indextype = 'text/image')
629           begin --{
630               /* 
631               ** This should be changed when we provide
632               ** per partition text partition to display
633               ** the number of rows in the corresponding
634               ** data partition.
635               */
636               update #result_body
637               set row_count = row_count(@dbid, @tableid)
638           end --}
639           else if (@indextype = 'global index')
640           begin --{
641               update #result_body
642               set row_count
643                   = row_count(@dbid, @tableid)
644           end --}
645           else
646           begin --{
647               if (@partitionname is not null)
648               begin
649                   select @datapage_ptnid = data_partitionid
650                   from syspartitions
651                   where id = @tableid and
652                       partitionid = @ptnid
653                   update #result_body
654                   set row_count = row_count(@dbid, @tableid,
655                           @datapage_ptnid)
656               end
657               else
658               begin
659                   update #result_body
660                   set row_count = row_count(@dbid, @tableid,
661                           s.data_partitionid)
662                   from syspartitions as s, #result_body as r
663                   where s.id = @tableid and
664                       s.partitionid = r.partition_id
665               end
666           end --}
667       end --}
668   
669       /* 
670       ** Pretty printing using sp_autoformat.
671       ** since the partition condition text could be big,
672       ** we print it in a separate section from other partition
673       ** information. But we print the partition condition for
674       ** each partition in certain order, so that the first
675       ** partition condition printed is the first partition
676       ** shown in previous section (#result_body).
677       */
678       exec sp_autoformat #result_body, 'partition_name, partition_id, compression_level, pages,row_count,segment,create_date', NULL, 'order by seqnum, partition_id'
679   
680       print " "
681   
682       /*
683       ** The following part prints the partition condition when applicable.
684       ** If we are not allowed to select from syscomments.text (@disallowmsg
685       ** is not null), then just print the disallow message. If the partition
686       ** condition text is hidden, we insert the hidden message in the result
687       ** table, so it will show in the place where partition condition text
688       ** will show.
689       */
690       if @disallowmsg is not null
691           print @disallowmsg
692       else
693       begin
694           if @hiddenmsg is not null
695               insert #result_cond values (@hiddenmsg, 0, 0, 0)
696   
697           exec sp_autoformat #result_cond, 'Partition_Conditions',
698               NULL, 'order by seqnum,colid2,colid'
699       end
700   
701       /*
702       ** Print the summary statistic information when more than one partitions
703       ** are displayed.
704       */
705       if @partitionname is not null
706           return (0)
707   
708       print " "
709   
710       select @avg_pages = avg(data_pages(@dbid, @tableid, @datapage_indid,
711               partitionid)),
712           @max_pages = max(data_pages(@dbid, @tableid, @datapage_indid,
713               partitionid)),
714           @min_pages = min(data_pages(@dbid, @tableid, @datapage_indid,
715               partitionid))
716       from syspartitions
717       where id = @tableid
718           and indid = @indexid
719   
720       select 'Avg_pages' = @avg_pages,
721           'Max_pages' = @max_pages,
722           'Min_pages' = @min_pages,
723           'Ratio(Max/Avg)' =
724           case
725               when @avg_pages = 0 then 0
726               else convert(float, @max_pages) / @avg_pages
727           end,
728           'Ratio(Min/Avg)' =
729           case
730               when @avg_pages = 0 then 0
731               else convert(float, @min_pages) / @avg_pages
732           end
733   
734       set nocount off
735       return (0)
736   


exec sp_procxmode 'sp_helpartition', 'AnyMode'
go

Grant Execute on sp_helpartition to public
go
RESULT SETS
sp_helpartition_rset_001

DEFECTS
 QJWI 5 Join or Sarg Without Index 516
 QJWI 5 Join or Sarg Without Index 567
 QJWI 5 Join or Sarg Without Index 613
 MEST 4 Empty String will be replaced by Single Space 251
 MTYP 4 Assignment type mismatch @range: smallint = int 87
 MTYP 4 Assignment type mismatch @hash: smallint = int 89
 MTYP 4 Assignment type mismatch @roundrobin: smallint = int 91
 MTYP 4 Assignment type mismatch @list: smallint = int 93
 MTYP 4 Assignment type mismatch @datapage_indid: smallint = int 398
 MTYP 4 Assignment type mismatch @typename: varchar(12) = varchar(28) 400
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 353
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 456
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 678
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 697
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
88
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
90
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
92
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
94
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: syspartitions.csyspartitions unique clustered
(id, indid, partitionid)
Intersection: {indid}
108
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {indid}
115
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {indid}
Uncovered: [id]
322
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: syspartitionkeys.csyspartitionkeys unique clustered
(id, indid, colid)
Intersection: {indid}
Uncovered: [colid]
322
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {indid}
Uncovered: [id]
342
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: syspartitions.csyspartitions unique clustered
(id, indid, partitionid)
Intersection: {indid}
Uncovered: [partitionid]
342
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: syspartitions.csyspartitions unique clustered
(id, indid, partitionid)
Intersection: {partitionid}
Uncovered: [id, indid]
608
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 108
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 112
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 115
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 174
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 194
 QTYP 4 Comparison type mismatch smallint = int 194
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 307
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 333
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 334
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 350
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 377
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 379
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 385
 QTYP 4 Comparison type mismatch smallint = int 385
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 402
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 424
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 452
 QTYP 4 Comparison type mismatch smallint = int 452
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 486
 QTYP 4 Comparison type mismatch smallint = int 486
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 515
 QTYP 4 Comparison type mismatch smallint = int 515
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 574
 QTYP 4 Comparison type mismatch smallint = int 574
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 612
 QTYP 4 Comparison type mismatch smallint = int 612
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 718
 QTYP 4 Comparison type mismatch smallint = int 718
 TNOI 4 Table with no index master..syscurconfigs master..syscurconfigs
 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..syscurconfigs  
 MGTP 3 Grant to public sybsystemprocs..sp_helpartition  
 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..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..syspartitionkeys  
 MGTP 3 Grant to public sybsystemprocs..syspartitions  
 MGTP 3 Grant to public sybsystemprocs..syssegments  
 MNER 3 No Error Check should check @@error after select into 104
 MNER 3 No Error Check should check return value of exec 117
 MNER 3 No Error Check should check @@error after insert 275
 MNER 3 No Error Check should check @@error after insert 289
 MNER 3 No Error Check should check @@error after update 303
 MNER 3 No Error Check should check @@error after insert 317
 MNER 3 No Error Check should check @@error after select into 332
 MNER 3 No Error Check should check return value of exec 353
 MNER 3 No Error Check should check return value of exec 432
 MNER 3 No Error Check should check return value of exec 439
 MNER 3 No Error Check should check @@error after select into 444
 MNER 3 No Error Check should check return value of exec 456
 MNER 3 No Error Check should check @@error after insert 489
 MNER 3 No Error Check should check @@error after insert 521
 MNER 3 No Error Check should check @@error after insert 523
 MNER 3 No Error Check should check @@error after insert 543
 MNER 3 No Error Check should check @@error after insert 578
 MNER 3 No Error Check should check @@error after insert 618
 MNER 3 No Error Check should check @@error after insert 620
 MNER 3 No Error Check should check @@error after update 636
 MNER 3 No Error Check should check @@error after update 641
 MNER 3 No Error Check should check @@error after update 653
 MNER 3 No Error Check should check @@error after update 659
 MNER 3 No Error Check should check return value of exec 678
 MNER 3 No Error Check should check @@error after insert 695
 MNER 3 No Error Check should check return value of exec 697
 MUCO 3 Useless Code Useless Brackets 132
 MUCO 3 Useless Code Useless Brackets 144
 MUCO 3 Useless Code Useless Brackets 161
 MUCO 3 Useless Code Useless Brackets 211
 MUCO 3 Useless Code Useless Brackets 255
 MUCO 3 Useless Code Useless Brackets 258
 MUCO 3 Useless Code Useless Brackets 264
 MUCO 3 Useless Code Useless Brackets 287
 MUCO 3 Useless Code Useless Brackets 330
 MUCO 3 Useless Code Useless Brackets 355
 MUCO 3 Useless Code Useless Brackets 395
 MUCO 3 Useless Code Useless Brackets 480
 MUCO 3 Useless Code Useless Brackets 498
 MUCO 3 Useless Code Useless Brackets 499
 MUCO 3 Useless Code Useless Brackets 500
 MUCO 3 Useless Code Useless Brackets 501
 MUCO 3 Useless Code Useless Brackets 502
 MUCO 3 Useless Code Useless Brackets 507
 MUCO 3 Useless Code Useless Brackets 541
 MUCO 3 Useless Code Useless Brackets 553
 MUCO 3 Useless Code Useless Brackets 554
 MUCO 3 Useless Code Useless Brackets 555
 MUCO 3 Useless Code Useless Brackets 556
 MUCO 3 Useless Code Useless Brackets 557
 MUCO 3 Useless Code Useless Brackets 563
 MUCO 3 Useless Code Useless Brackets 588
 MUCO 3 Useless Code Useless Brackets 589
 MUCO 3 Useless Code Useless Brackets 590
 MUCO 3 Useless Code Useless Brackets 591
 MUCO 3 Useless Code Useless Brackets 592
 MUCO 3 Useless Code Useless Brackets 598
 MUCO 3 Useless Code Useless Brackets 617
 MUCO 3 Useless Code Useless Brackets 626
 MUCO 3 Useless Code Useless Brackets 628
 MUCO 3 Useless Code Useless Brackets 639
 MUCO 3 Useless Code Useless Brackets 647
 MUCO 3 Useless Code Useless Brackets 706
 MUCO 3 Useless Code Useless Brackets 735
 QAFM 3 Var Assignment from potentially many rows 87
 QAFM 3 Var Assignment from potentially many rows 89
 QAFM 3 Var Assignment from potentially many rows 91
 QAFM 3 Var Assignment from potentially many rows 93
 QAFM 3 Var Assignment from potentially many rows 400
 QAFM 3 Var Assignment from potentially many rows 422
 QAFM 3 Var Assignment from potentially many rows 649
 QCTC 3 Conditional Table Creation 104
 QCTC 3 Conditional Table Creation 332
 QDIS 3 Check correct use of 'select distinct' 224
 QGWO 3 Group by/Distinct/Union without order by 605
 QISO 3 Set isolation level 78
 QJWT 3 Join or Sarg Without Index on temp table 306
 QJWT 3 Join or Sarg Without Index on temp table 349
 QJWT 3 Join or Sarg Without Index on temp table 386
 QJWT 3 Join or Sarg Without Index on temp table 664
 QNAJ 3 Not using ANSI Inner Join 114
 QNAJ 3 Not using ANSI Inner Join 225
 QNAJ 3 Not using ANSI Inner Join 304
 QNAJ 3 Not using ANSI Inner Join 347
 QNAJ 3 Not using ANSI Inner Join 383
 QNAJ 3 Not using ANSI Inner Join 513
 QNAJ 3 Not using ANSI Inner Join 610
 QNAJ 3 Not using ANSI Inner Join 662
 QNUA 3 Should use Alias: Column uid should use alias o 104
 QPNC 3 No column in condition 174
 QPNC 3 No column in condition 195
 QPNC 3 No column in condition 197
 QPNC 3 No column in condition 487
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {id}
156
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syspartitionkeys.csyspartitionkeys unique clustered
(id, indid, colid)
Intersection: {id}
226
 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]
227
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: syspartitionkeys.csyspartitionkeys unique clustered
(id, indid, colid)
Intersection: {colid, id}
Uncovered: [indid]
227
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {id}
305
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {id}
319
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {id}
348
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered
(id, number, colid2, colid, texttype)
Intersection: {id}
437
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syspartitions.csyspartitions unique clustered
(id, indid, partitionid)
Intersection: {indid, id}
451
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered
(id, number, colid2, colid, texttype)
Intersection: {id}
526
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syspartitions.csyspartitions unique clustered
(id, indid, partitionid)
Intersection: {indid, id}
573
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered
(id, number, colid2, colid, texttype)
Intersection: {id}
607
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syspartitions.csyspartitions unique clustered
(id, indid, partitionid)
Intersection: {indid, id}
611
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered
(id, number, colid2, colid, texttype)
Intersection: {id}
623
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syspartitions.csyspartitions unique clustered
(id, indid, partitionid)
Intersection: {partitionid, id}
651
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syspartitions.csyspartitions unique clustered
(id, indid, partitionid)
Intersection: {id}
663
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syspartitions.csyspartitions unique clustered
(id, indid, partitionid)
Intersection: {indid, id}
717
 QSWV 3 Sarg with variable @ptntype, Candidate Index: spt_values.spt_valuesclust clustered(number, type) F 402
 CRDO 2 Read Only Cursor Marker (has for read only clause) 224
 MRST 2 Result Set Marker 720
 MSUB 2 Subquery Marker 138
 MSUB 2 Subquery Marker 154
 MSUB 2 Subquery Marker 191
 MSUB 2 Subquery Marker 428
 MSUB 2 Subquery Marker 436
 MSUB 2 Subquery Marker 450
 MSUC 2 Correlated Subquery Marker 105
 MSUC 2 Correlated Subquery Marker 109
 MSUC 2 Correlated Subquery Marker 304
 MSUC 2 Correlated Subquery Marker 320
 MSUC 2 Correlated Subquery Marker 339
 MSUC 2 Correlated Subquery Marker 566
 MSUC 2 Correlated Subquery Marker 605
 MTR1 2 Metrics: Comments Ratio Comments: 35% 42
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 74 = 79dec - 7exi + 2 42
 MTR3 2 Metrics: Query Complexity Complexity: 342 42
 PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, o=sybsystemprocs..sysobjects} 0 104
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..syspartitions} 0 105
 PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, sv=master..spt_values} 0 109
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, p=sybsystemprocs..syspartitionkeys} 0 224
 PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, p=sybsystemprocs..syspartitionkeys} 0 320
 PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, p=sybsystemprocs..syspartitions} 0 339
 PRED_QUERY_COLLECTION 2 {p=sybsystemprocs..syspartitions, s=sybsystemprocs..syssegments} 0 490
 PRED_QUERY_COLLECTION 2 {p=sybsystemprocs..syspartitions, s=sybsystemprocs..syssegments} 0 566
 PRED_QUERY_COLLECTION 2 {p=sybsystemprocs..syspartitions, s=sybsystemprocs..syssegments} 0 579
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscomments, p=sybsystemprocs..syspartitions} 0 605

DEPENDENCIES
PROCS AND TABLES USED
writes table tempdb..#all_tables (1) 
calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   read_writes table tempdb..#colinfo_af (1) 
   reads table tempdb..syscolumns (1)  
   reads table master..systypes (1)  
   calls proc sybsystemprocs..sp_autoformat  
   calls proc sybsystemprocs..sp_namecrack  
   reads table tempdb..systypes (1)  
reads table sybsystemprocs..syscomments  
reads table sybsystemprocs..syssegments  
writes table tempdb..#result_head (1) 
reads table sybsystemprocs..sysindexes  
writes table tempdb..#result_all (1) 
reads table sybsystemprocs..syspartitions  
reads table master..syscurconfigs (1)  
writes table tempdb..#result_body (1) 
reads table master..spt_values (1)  
writes table tempdb..#result_cond (1) 
reads table sybsystemprocs..syscolumns  
calls proc sybsystemprocs..sp_getmessage  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
read_writes table tempdb..#col_names (1) 
reads table sybsystemprocs..sysobjects  
reads table sybsystemprocs..syspartitionkeys  

CALLERS
called by proc sybsystemprocs..sp_help