DatabaseProcApplicationCreatedLinks
sybsystemprocssp_helpartition  14 déc. 14Defects Propagation 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 or replace 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' or type = 'RS'))
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 (@indextype != 'base table' and @indexid = 1) then 'inherit from index'
499                   when (@ptnlevel & 128 > 0) then 'none'
500                   when ((@indexid != 0 and @indexid != 1) and (@ptnlevel & 512 > 0)) then 'index compressed'
501                   when (@indexid != 0 and @indexid != 1) then 'inherit from index'
502                   /* Next part are for data */
503                   when (@ptnlevel & 32 > 0) then 'page'
504                   when (@ptnlevel & 16 > 0) then 'row'
505                   when (@tablevel & 4096 > 0) then 'inherit from table' /*table row compressed*/
506                   when (@tablevel & 8192 > 0) then 'inherit from table' /*table page compressed*/
507                   else "none"
508               end,
509               data_pages(@dbid, @tableid, @datapage_indid, @ptnid),
510               case
511                   when (@datapage_indid > 1) then 0
512                   else row_count(@dbid, @tableid, @ptnid)
513               end,
514               s.name,
515               p.crdate,
516               0
517           from syspartitions p, syssegments s
518           where p.id = @tableid
519               and p.indid = @indexid
520               and p.segment = s.segment
521               and p.partitionid = @ptnid
522   
523           /* Get partition condition */
524           if @ptntype in (@roundrobin, @hash)
525               insert #result_cond values (NULL, 0, 0, 0)
526           else if (@disallowmsg is null) and (@hiddenmsg is null)
527               insert #result_cond
528               select c.text, c.number, c.colid, c.colid2
529               from syscomments c
530               where c.id = @condid
531                   and c.partitionid = @ptnid
532   
533       end
534   
535       /*
536       ** If we come here, the partition name is not specified,
537       ** we print the information for all partitions under this
538       ** index.
539       ** If range/list/hash partition, print the partitions in
540       ** order their ptncond is stored in syscomments, this order
541       ** is stored in the cnumber field of the syscomments row.
542       */
543       else
544       begin
545           if (@ptntype = @roundrobin)
546           begin
547               insert #result_body
548               select 'partition_name' =
549                   case
550                       when @use_savedname = 1
551                       then p.cdataptnname
552                       else p.name
553                   end,
554                   p.partitionid,
555                   'compression_level' =
556                   case
557                       when (@indextype != 'base table' and @indexid = 1) then 'inherit from index'
558                       when (isnull(p.status, 0) & 128 > 0) then 'none'
559                       when ((@indexid != 0 and @indexid != 1) and (isnull(p.status, 0) & 512 > 0)) then 'index compressed'
560                       when (@indexid != 0 and @indexid != 1) then 'inherit from index'
561                       /* Next part are for data */
562                       when (isnull(p.status, 0) & 32 > 0) then 'page'
563                       when (isnull(p.status, 0) & 16 > 0) then 'row'
564                       when (@tablevel & 4096 > 0) then 'inherit from table' /*table row compressed*/
565                       when (@tablevel & 8192 > 0) then 'inherit from table' /*table page compressed*/
566                       else "none"
567                   end,
568                   data_pages(@dbid, @tableid, @datapage_indid,
569                       p.partitionid),
570                   case
571                       when (@datapage_indid > 1) then 0
572                       else row_count(@dbid, @tableid, p.partitionid)
573                   end,
574                       (select s.name from syssegments s
575                       where s.segment = p.segment),
576   
577                   p.crdate,
578   
579                   0
580               from syspartitions p
581               where p.id = @tableid
582                   and p.indid = @indexid
583           end
584           else
585           begin
586               insert #result_body
587               select 'partition_name' =
588                   case
589                       when @use_savedname = 1
590                       then p.cdataptnname
591                       else p.name
592                   end,
593                   p.partitionid,
594                   'compression_level' =
595                   case
596                       when (@indextype != 'base table' and @indexid = 1) then 'inherit from index'
597                       when (isnull(p.status, 0) & 128 > 0) then 'none'
598                       when ((@indexid != 0 and @indexid != 1) and (isnull(p.status, 0) & 512 > 0)) then 'index compressed'
599                       when (@indexid != 0 and @indexid != 1) then 'inherit from index'
600                       /* Next part are for data */
601                       when (isnull(p.status, 0) & 32 > 0) then 'page'
602                       when (isnull(p.status, 0) & 16 > 0) then 'row'
603                       when (@tablevel & 4096 > 0) then 'inherit from table' /*table row compressed*/
604                       when (@tablevel & 8192 > 0) then 'inherit from table' /*table page compressed*/
605                       else "none"
606                   end,
607                   data_pages(@dbid, @tableid, @datapage_indid,
608                       p.partitionid),
609                   case
610                       when (@datapage_indid > 1) then 0
611                       else row_count(@dbid, @tableid, p.partitionid)
612                   end,
613                   s.name,
614   
615                   p.crdate,
616   
617                       (select distinct c.number
618                       from syscomments c
619                       where c.id = @condid
620                           and c.partitionid = p.partitionid)
621   
622               from syspartitions p, syssegments s
623               where p.id = @tableid
624                   and p.indid = @indexid
625                   and p.segment = s.segment
626           end
627   
628           /* Get partition condition */
629           if (@ptntype = @roundrobin or @ptntype = @hash)
630               insert #result_cond values (NULL, 0, 0, 0)
631           else if (@disallowmsg is null) and (@hiddenmsg is null)
632               insert #result_cond
633               select c.text, c.number, c.colid, c.colid2
634               from syscomments c
635               where c.id = @condid
636       end
637   
638       if (@indexid > 1)
639       begin --{
640           if (@indextype = 'text/image')
641           begin --{
642               /* 
643               ** This should be changed when we provide
644               ** per partition text partition to display
645               ** the number of rows in the corresponding
646               ** data partition.
647               */
648               update #result_body
649               set row_count = row_count(@dbid, @tableid)
650           end --}
651           else if (@indextype = 'global index')
652           begin --{
653               update #result_body
654               set row_count
655                   = row_count(@dbid, @tableid)
656           end --}
657           else
658           begin --{
659               if (@partitionname is not null)
660               begin
661                   select @datapage_ptnid = data_partitionid
662                   from syspartitions
663                   where id = @tableid and
664                       partitionid = @ptnid
665                   update #result_body
666                   set row_count = row_count(@dbid, @tableid,
667                           @datapage_ptnid)
668               end
669               else
670               begin
671                   update #result_body
672                   set row_count = row_count(@dbid, @tableid,
673                           s.data_partitionid)
674                   from syspartitions as s, #result_body as r
675                   where s.id = @tableid and
676                       s.partitionid = r.partition_id
677               end
678           end --}
679       end --}
680   
681       /* 
682       ** Pretty printing using sp_autoformat.
683       ** since the partition condition text could be big,
684       ** we print it in a separate section from other partition
685       ** information. But we print the partition condition for
686       ** each partition in certain order, so that the first
687       ** partition condition printed is the first partition
688       ** shown in previous section (#result_body).
689       */
690       exec sp_autoformat #result_body, 'partition_name, partition_id, compression_level, pages,row_count,segment,create_date', NULL, 'order by seqnum, partition_id'
691   
692       print " "
693   
694       /*
695       ** The following part prints the partition condition when applicable.
696       ** If we are not allowed to select from syscomments.text (@disallowmsg
697       ** is not null), then just print the disallow message. If the partition
698       ** condition text is hidden, we insert the hidden message in the result
699       ** table, so it will show in the place where partition condition text
700       ** will show.
701       */
702       if @disallowmsg is not null
703           print @disallowmsg
704       else
705       begin
706           if @hiddenmsg is not null
707               insert #result_cond values (@hiddenmsg, 0, 0, 0)
708   
709           exec sp_autoformat #result_cond, 'Partition_Conditions',
710               NULL, 'order by seqnum,colid2,colid'
711       end
712   
713       /*
714       ** Print the summary statistic information when more than one partitions
715       ** are displayed.
716       */
717       if @partitionname is not null
718           return (0)
719   
720       print " "
721   
722       select @avg_pages = avg(data_pages(@dbid, @tableid, @datapage_indid,
723               partitionid)),
724           @max_pages = max(data_pages(@dbid, @tableid, @datapage_indid,
725               partitionid)),
726           @min_pages = min(data_pages(@dbid, @tableid, @datapage_indid,
727               partitionid))
728       from syspartitions
729       where id = @tableid
730           and indid = @indexid
731   
732       select 'Avg_pages' = @avg_pages,
733           'Max_pages' = @max_pages,
734           'Min_pages' = @min_pages,
735           'Ratio(Max/Avg)' =
736           case
737               when @avg_pages = 0 then 0
738               else convert(float, @max_pages) / @avg_pages
739           end,
740           'Ratio(Min/Avg)' =
741           case
742               when @avg_pages = 0 then 0
743               else convert(float, @min_pages) / @avg_pages
744           end
745   
746       set nocount off
747       return (0)
748   


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 520
 QJWI 5 Join or Sarg Without Index 575
 QJWI 5 Join or Sarg Without Index 625
 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(255) 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 690
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 709
 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]
620
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: syspartitions.csyspartitions unique clustered
(id, indid, partitionid)
Intersection: {partitionid}
Uncovered: [indid]
676
 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 519
 QTYP 4 Comparison type mismatch smallint = int 519
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 582
 QTYP 4 Comparison type mismatch smallint = int 582
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 624
 QTYP 4 Comparison type mismatch smallint = int 624
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 730
 QTYP 4 Comparison type mismatch smallint = int 730
 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
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 107
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 107
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 115
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 115
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 139
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 156
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 173
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 193
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitionkeys.id: Warning message on syspartitionkeys 226
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 227
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitionkeys.id: Warning message on syspartitionkeys 227
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 305
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 319
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitionkeys.id: Warning message on syspartitionkeys 322
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 341
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 348
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 384
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 430
 MAW1 3 Warning message on %name% sybsystemprocs..syscomments.id: Warning message on syscomments 437
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 451
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 478
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 485
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 518
 MAW1 3 Warning message on %name% sybsystemprocs..syscomments.id: Warning message on syscomments 530
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 581
 MAW1 3 Warning message on %name% sybsystemprocs..syscomments.id: Warning message on syscomments 619
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 623
 MAW1 3 Warning message on %name% sybsystemprocs..syscomments.id: Warning message on syscomments 635
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 663
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 675
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 729
 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 525
 MNER 3 No Error Check should check @@error after insert 527
 MNER 3 No Error Check should check @@error after insert 547
 MNER 3 No Error Check should check @@error after insert 586
 MNER 3 No Error Check should check @@error after insert 630
 MNER 3 No Error Check should check @@error after insert 632
 MNER 3 No Error Check should check @@error after update 648
 MNER 3 No Error Check should check @@error after update 653
 MNER 3 No Error Check should check @@error after update 665
 MNER 3 No Error Check should check @@error after update 671
 MNER 3 No Error Check should check return value of exec 690
 MNER 3 No Error Check should check @@error after insert 707
 MNER 3 No Error Check should check return value of exec 709
 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 503
 MUCO 3 Useless Code Useless Brackets 504
 MUCO 3 Useless Code Useless Brackets 505
 MUCO 3 Useless Code Useless Brackets 506
 MUCO 3 Useless Code Useless Brackets 511
 MUCO 3 Useless Code Useless Brackets 545
 MUCO 3 Useless Code Useless Brackets 557
 MUCO 3 Useless Code Useless Brackets 558
 MUCO 3 Useless Code Useless Brackets 559
 MUCO 3 Useless Code Useless Brackets 560
 MUCO 3 Useless Code Useless Brackets 562
 MUCO 3 Useless Code Useless Brackets 563
 MUCO 3 Useless Code Useless Brackets 564
 MUCO 3 Useless Code Useless Brackets 565
 MUCO 3 Useless Code Useless Brackets 571
 MUCO 3 Useless Code Useless Brackets 596
 MUCO 3 Useless Code Useless Brackets 597
 MUCO 3 Useless Code Useless Brackets 598
 MUCO 3 Useless Code Useless Brackets 599
 MUCO 3 Useless Code Useless Brackets 601
 MUCO 3 Useless Code Useless Brackets 602
 MUCO 3 Useless Code Useless Brackets 603
 MUCO 3 Useless Code Useless Brackets 604
 MUCO 3 Useless Code Useless Brackets 610
 MUCO 3 Useless Code Useless Brackets 629
 MUCO 3 Useless Code Useless Brackets 638
 MUCO 3 Useless Code Useless Brackets 640
 MUCO 3 Useless Code Useless Brackets 651
 MUCO 3 Useless Code Useless Brackets 659
 MUCO 3 Useless Code Useless Brackets 718
 MUCO 3 Useless Code Useless Brackets 747
 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 661
 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 617
 QISO 3 Set isolation level 78
 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 517
 QNAJ 3 Not using ANSI Inner Join 622
 QNAJ 3 Not using ANSI Inner Join 674
 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}
530
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syspartitions.csyspartitions unique clustered
(id, indid, partitionid)
Intersection: {indid, id}
581
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered
(id, number, colid2, colid, texttype)
Intersection: {id}
619
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syspartitions.csyspartitions unique clustered
(id, indid, partitionid)
Intersection: {indid, id}
623
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered
(id, number, colid2, colid, texttype)
Intersection: {id}
635
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syspartitions.csyspartitions unique clustered
(id, indid, partitionid)
Intersection: {partitionid, 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}
729
 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 732
 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 574
 MSUC 2 Correlated Subquery Marker 617
 MTR1 2 Metrics: Comments Ratio Comments: 34% 42
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 76 = 81dec - 7exi + 2 42
 MTR3 2 Metrics: Query Complexity Complexity: 364 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 574
 PRED_QUERY_COLLECTION 2 {p=sybsystemprocs..syspartitions, s=sybsystemprocs..syssegments} 0 587
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscomments, p=sybsystemprocs..syspartitions} 0 617

DATA PROPAGATION detailed
ColumnWritten To
@tabnamesp_helpartition_rset_001.Avg_pages °.Max_pages °.Min_pages °.Ratio(Max/Avg) °.Ratio(Min/Avg)

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

CALLERS
called by proc sybsystemprocs..sp_help