DatabaseProcApplicationCreatedLinks
sybsystemprocssp_modifystats  14 déc. 14Defects Propagation Dependencies

1     
2     
3     /*
4     ** Messages for "sp_modifystats"
5     ** 17460, "Object must be in the current database."
6     ** 17563, "The table does not have a column named '%1!'."
7     ** 18118, "Object '%1!' does not exist in this database."
8     ** 18162, "'%1!' is a not a user table. '%2!' can be used only on user tables."
9     ** 18884, "No statistics exist for column '%1!'."
10    ** 18921, "Unable to execute stored procedure '%1!'.This version of the procedure is based on a '%2!' byte column ID and is incompatible with the server's column ID size.
11    ** 18922, "Command failed. The 'value' argument for 'MODIFY_DENSITY' must be greater than or equal to 0.0 and not more than 1.0 when the 'absolute' method is specified.
12    ** 18923, "The density value of column group '%1!' exceeded 1.0 and has been set to 1.0. Processing continues.
13    ** 19569, "UPDATE STATISTICS permission denied on object '%1!'."
14    */
15    
16    /*
17    ** IMPORTANT NOTE:
18    ** This stored procedure uses the built-in function object_id() in the
19    ** where clause of a select query. If you intend to change this query
20    ** or use the object_id() or db_id() builtin in this procedure, please read the
21    ** READ.ME file in the $DBMS/generic/sproc directory to ensure that the rules
22    ** pertaining to object-id's and db-id's outlined there, are followed.
23    */
24    
25    create or replace procedure sp_modifystats
26        @objname varchar(767) = null, /* user table name */
27        @column_group varchar(1200) = null, /* column name */
28        @option varchar(60) = null, /* statistic to be modified */
29        @opt_param1 varchar(255) = null, /* optional param */
30        @opt_param2 varchar(255) = null, /* optional param */
31        @opt_param3 varchar(255) = null /* optional param */
32    as
33        /*
34        **
35        **	Description: Allow sa to modify statistics stored
36        **				in sysstatistics.
37        **
38        **	Argument:	all uses of sp_modifystats require at least
39        **			three arguments: the table in question;
40        **			the column or column group (note that this
41        **			can be null); and the option.  Since different
42        **			options may require different arguments,several
43        **			@opt_param varchar(255) args are provided.
44        **			Please do not modify the datatype of these
45        **			params,	rather use the convert function within
46        **			your option.
47        **			Additional @opt_param parameters may be added
48        **			as they are needed.
49        **
50        **	Options:	"REMOVE_SKEW_FROM_DENSITY"  -
51        **				This option allows sa to change the
52        **				total density of a column to be equal
53        **				to the range density which is useful
54        **				when data skew is present.
55        **				This will also update the total density of
56        **				any composite column statistics for which
57        **				this column is the leading attribute.
58        **				Most commonly, a composite index for which
59        **				this column is the leading attribute would
60        **				produce such composite column statistics,
61        **				but they can also be produced by a
62        **				composite update column statistics such as
63        **				update statistics t1(a,b,c) 
64        **				REQUIRES @objname, @column_group, @option.
65        **			"REMOVE_STICKINESS"  -
66        **				This option allows sa to disable the
67        **				stickiness behavior of a column or table
68        **				If the stickiness should be removed for
69        **				all columns in a table, the @column_group 
70        **				should be NULL
71        **				If a single column should have the stickiness
72        **				removed, then the @column_group should have
73        **				that column
74        **				REQUIRES @objname, , @option.
75        **			"MODIFY_DEFAULT_SELECTIVITY" - if saragble values 
76        **				are not available for histogramming, 
77        **				then use these defaults for
78        **				predicate selectivity ">", "<", ">=", "<=" 
79        **				only applicable to single columns.
80        **				The description in "MODIFY_DENSITY" applies
81        **			        to this option except that 
82        **				{inequality | inbetween} is used instead of
83        **				{range | total}
84        **				REQUIRES @objname, @column_group, @option,
85        **					@opt_param1, @opt_param2,
86        **					@opt_param3
87        **			"MODIFY_DENSITY" or "MODIFY_UNIQUE" -
88        **				This option provides a very granular
89        **				mechanism to modify the range density,
90        **				total density, range unique or total unique
91        **				for a column or column group.
92        **				Explanation of syntax:
93        **				"[database].[owner].table" -
94        **					object name you wish to modify
95        **				{ "column_group" | "all" } - the column
96        **					or groups of columns to modify.
97        **				Rules: "all" will modify all columns
98        **						for this table
99        **					"a1" will modify column a1
100       **					"a1,a2,a3" will
101       **						modify the group a1,a2,a3
102       **					"a1,%,a3" will modify
103       **						the groups a1,a2,a3 and 
104       **						a1,a5,a3, etc.
105       **					"a1,%" will modify
106       **						the groups a1,a2
107       **						and a1,a2,a3, etc. but
108       **						not a1.
109       **					"a1%" will modify
110       **						the groups a1,a2 and
111       **						a1,a2,a3 AS WELL AS a1.
112       **				{ range | total } - for MODIFY_DENSITY the
113       **					range cell or total cell density
114       **					for MODIFY_UNIQUE the
115       **					range cell or total cell unique
116       **					
117       **				{ absolute | factor } - type of
118       **					modification to do. If absolute,
119       **					then the value argument is the
120       **					value you want.  If factor, then
121       **					multiply existing value by the
122       **					value argument
123       **				value - the target value.  Must be >= 0
124       **					and <= 1.0 if absolute is
125       **					specified
126       **				REQUIRES @objname,@column_group, @option,
127       **					@opt_param1, @opt_param2,
128       **					@opt_param3
129       **
130       **
131       **	Future Info:	Other options can be added in the future
132       **				using the @option parameter.
133       **
134       **	Dependencies:	This proc relies on the object_id built-in,
135       **			and the db_name() built-in. Additionally,
136       **			it must be created with updates to system
137       **			tables enabled.
138       **
139       **	Errors:		This proc relies on the following errors:
140       **				17460, 17563, 18118, 18162
141       **			Errors 18884, 18921, 18922, 18923
142       **				are installed for this proc.
143       **
144       **	Version:	This proc is for ASE 11.9.x and beyond
145       **
146       **	Usage:		sp_modifystats tabname, column_group, opt
147       **
148       **	History:	9/16/99	(mciccone)
149       **			2/1/01	(dwein - changed structure of proc
150       **					to improve modularization)
151       **			2/1/01	(dwein - added MODIFY_DENSITY)
152       **			3/16/11 (edwins - added REMOVE_STICKINESS)
153       */
154   
155       /*
156       ** Note that any local variables needed for your module
157       ** should be declared within that module only.  @rows,
158       ** @err, and @colid are retained here to encourage their use.
159       */
160   
161       declare @rows int /* Variable to check @@rowcount */
162       declare @err int /* Variable to check @@error */
163       declare @colid int /* Variable to hold colid from syscolumns */
164       declare @colidsize int /* Variable to hold colid size for the server */
165       declare @role_name varchar(30) /* Stores role name */
166       declare @user_role_per int /* Variable to indicate whether user has
167       ** permission to execute the procedure
168       ** through any role
169       */
170   
171       declare @nullarg char(1)
172       declare @dummy int
173       declare @status int
174       declare @gp_enabled int
175       declare @dbname varchar(255)
176   
177       select @status = 1
178   
179       set nocount on
180   
181       /*
182       ** Check colid size to avoid executing this proc
183       ** on a server with different colid size.
184       */
185       select @colidsize = length from syscolumns
186       where id = object_id("syscolumns") and name = "colid"
187   
188       if (@colidsize != 2)
189       begin
190           /*
191           ** 18921, "Unable to execute stored procedure '%1!'.This version of the
192           ** procedure is based on a '%2!' byte column ID and is incompatible 
193           ** with the server's column ID size."
194           */
195           raiserror 18921, "sp_modifystats", "2"
196           return (1)
197       end
198   
199       /* Check Usage */
200   
201       /*
202       ** Note that when adding modules to this procedure they must
203       ** be added to this check.
204       */
205   
206       if (@objname is null) or (@option not in (
207                   "REMOVE_SKEW_FROM_DENSITY",
208                   "MODIFY_DENSITY",
209                   "REMOVE_STICKINESS",
210                   "MODIFY_UNIQUE",
211                   "MODIFY_DEFAULT_SELECTIVITY"))
212       begin
213           /*
214           ** When adding an option, be sure to add it to this usage
215           ** output.
216           */
217           print "Usage: sp_modifystats objname, column_group, option"
218           print "Valid Options:"
219           print "REMOVE_SKEW_FROM_DENSITY,"
220           print "MODIFY_DENSITY,"
221           print "REMOVE_STICKINESS,"
222           print "MODIFY_UNIQUE,"
223           print "MODIFY_DEFAULT_SELECTIVITY"
224           return (1)
225       end
226   
227       /* Only operate on tables in current database */
228       if @objname like "%.%.%" and
229           substring(@objname, 1, charindex(".", @objname) - 1) != db_name()
230       begin
231           /* 17460, "Object must be in the current database." */
232           raiserror 17460
233           return (1)
234       end
235   
236       /* Check to see if the @objname is in sysobjects. */
237       if not exists (select * from sysobjects
238               where id = object_id(@objname))
239       begin
240           /* 18118, "Object '%1!' does not exist in this database." */
241           raiserror 18118, @objname
242           return (1)
243       end
244   
245       /*
246       ** Make sure @objname in NOT a system table.
247       ** Check status bit within sysobjects.systats. This is same
248       ** method the server uses internally to determine if an object
249       ** is a system table.
250       */
251       if ((select (sysstat & 15) from sysobjects where
252                       id = object_id(@objname)) = 1)
253       begin
254           /*
255           ** 18162: '%1!' is a not a user table.
256           ** '%2!' can be used only on user tables.
257           */
258           raiserror 18162, @objname, "sp_modifystats"
259           return (1)
260       end
261   
262       /*
263       ** Check for UPDATE STATISTICS permission on object.
264       ** Will have to check for groups and roles that the user belongs to.
265       */
266   
267       /*
268       ** Initialise @user_role_per to 0, indicating user does not have permission
269       ** for UPDATE STATISTICS through any user defined roles or contained roles
270       */
271       select @user_role_per = 0
272   
273       declare role_name cursor for
274       select srvro.name from sysprotects p1,
275           master.dbo.syssrvroles srvro,
276           sysroles ro
277       where p1.id = object_id(@objname)
278           and p1.uid = ro.lrid
279           and ro.id = srvro.srid
280           and p1.action = 326
281       for read only
282   
283       open role_name
284   
285       fetch role_name into @role_name
286       while (@@sqlstatus = 0 and @user_role_per != 1)
287       begin
288           if @role_name is not null
289           begin
290               if (has_role(@role_name, 1) > 0)
291               begin
292                   /*
293                   ** User has role which has
294                   ** permission for UPDATE STATISTICS
295                   */
296                   select @user_role_per = 1
297               end
298           end
299           fetch role_name into @role_name
300       end
301       close role_name
302       deallocate cursor role_name
303   
304   
305       /* 
306       ** If granular permissions is not enabled then if the user doesn't have
307       ** sa_role he needs to have 'UPDATE STATISTICS' permission on the object.
308       ** If granular permissions is enabled then if the user doesn't have
309       ** 'manage any statistics' permission in the database then the user must have
310       ** 'UPDATE STATISTICS' permission on the object.
311       */
312       /* Only operate on tables in current database */
313       select @dbname = db_name()
314       select @nullarg = NULL
315       execute @status = sp_aux_checkroleperm "sa_role", "manage any statistics",
316           @dbname, @gp_enabled output
317       if ((@status != 0)
318               /*
319               ** If user is the owner of the object, then has UPDATE STATISTICS permission
320               ** on the object
321               */
322               and not exists (select id from sysobjects
323                   where id = object_id(@objname)
324                       and uid = user_id())
325   
326               and ((select max((abs(p.uid - u2.gid) * 2) +
327                   ((p.protecttype / 2) ^ 1))
328                   from sysprotects p, sysusers u2
329                   where p.id = object_id(@objname)
330                       and u2.uid = user_id()
331   
332                       /* get rows for public, current users, user's groups */
333                       and (p.uid = 0 /* get rows for public */
334                           or p.uid = user_id() /* current user */
335                           or p.uid = u2.gid) /* users group */
336                       and (p.action in (326)) /* check for UPDATE STATISTICS privilege */
337                   ) & 1) is NULL
338   
339               /*
340               ** If @user_role_per = 0, then user doesnot have permission through any 
341               ** user defined roles or contained roles 
342               */
343               and @user_role_per = 0)
344       begin
345           /* 19569, "UPDATE STATISTICS permission denied on object '%1!'." */
346           raiserror 19569, @objname
347           if (@gp_enabled = 1)
348               select @dummy = proc_auditperm("manage any statistics", @status)
349           return (1)
350       end
351   
352       /* For Auditing */
353       if (@status = 0)
354       begin
355           if (@gp_enabled = 0)
356               select @dummy = proc_role("sa_role")
357           else
358               select @dummy = proc_auditperm("manage any statistics", @status)
359       end
360   
361       if @@trancount = 0
362       begin
363           set chained off
364       end
365   
366       set transaction isolation level 1
367       if @option = "REMOVE_SKEW_FROM_DENSITY"
368       /**Update total density for specified column**/
369       begin
370   
371           /*
372           ** The first action of any module is to setup
373           ** module specific variables and validate
374           ** any module-specific usage.
375           */
376   
377           /* Validate @columngroup value, as it is required by this module */
378           /* Step 1 : make sure it isn't null */
379           if (@column_group is null)
380           begin
381               print "Usage for REMOVE_SKEW_FROM_DENSITY option:"
382               print "sp_modifystats table_name, column_name, REMOVE_SKEW_FROM_DENSITY"
383               return (1)
384           end
385           /* Step 2: Check to see if the @column_group is in syscolumns */
386           if not exists (select * from syscolumns
387                   where id = object_id(@objname) and name = @column_group)
388           begin
389               /* 17563: "The table does not have a column named '%1!'." */
390               raiserror 17563, @column_group
391               return (1)
392           end
393   
394           /* We are ready to proceed with the actual work. */
395           select @colid = (select colid from syscolumns
396                   where id = object_id(@objname) and name = @column_group)
397           begin tran
398           /*
399           ** Change the total density & update STATISTICS_EDITED flag
400           */
401           update sysstatistics set c3 = c2,
402               c1 = convert(varbinary, (convert(int, c1) | 2))
403           where convert(smallint, substring(colidarray, 1, 2)) = @colid
404               and id = object_id(@objname)
405               and formatid = 100
406   
407           select @rows = @@rowcount, @err = @@error
408           if @err != 0
409           begin
410               select @err
411               rollback tran
412               return (1)
413           end
414           if @rows >= 1
415           begin
416               /* Successful update */
417               commit tran
418               return (0)
419           end
420           else
421           begin
422               /* 18884: No statistics exist for column '%1!'. */
423               raiserror 18884, @column_group
424               rollback tran
425               return (1)
426           end
427       end
428       /* end of REMOVE_SKEW_FROM_DENSITY module */
429   
430       else if @option = "REMOVE_STICKINESS"
431       /** remove any stickiness for the specified column or table**/
432       begin
433           /* Step 1 : is it a column or the entire table? */
434           if (@column_group is null)
435           begin
436               /* remove stickiness for all columns in table */
437               begin tran
438               /*
439               ** remove the sticky flags & update STATISTICS_EDITED flag
440               ** sampling 		256
441               ** step count 		512
442               ** tuning factor 	1024
443               ** partial hashing 	2048
444               ** no hashing 		4096
445               ** resetting mask 2147475711 = ~(256 + 512 + 1024 + 2048 + 4096)
446               */
447               update sysstatistics set
448                   c1 = convert(varbinary, ((convert(int, c1) | 2) & 2147475711))
449               where id = object_id(@objname)
450                   and formatid = 100
451   
452               select @err = @@error
453               if @err != 0
454               begin
455                   select @err
456                   rollback tran
457                   return (1)
458               end
459   
460               /* success */
461               commit tran
462               return (0)
463           end
464           else
465           begin
466               /* Step 2: Check to see if the @column_group is in syscolumns */
467               if not exists (select * from syscolumns
468                       where id = object_id(@objname) and name = @column_group)
469               begin
470                   /* 17563: "The table does not have a column named '%1!'." */
471                   raiserror 17563, @column_group
472                   return (1)
473               end
474   
475               /* We are ready to proceed with the actual work. */
476               select @colid = (select colid from syscolumns
477                       where id = object_id(@objname) and name = @column_group)
478               begin tran
479               /*
480               ** remove the sticky flags & update STATISTICS_EDITED flag
481               ** sampling 		256
482               ** step count 		512
483               ** tuning factor 	1024
484               ** partial hashing 	2048
485               ** no hashing 		4096
486               ** resetting mask 2147475711 = ~(256 + 512 + 1024 + 2048 + 4096)
487               */
488               update sysstatistics set
489                   c1 = convert(varbinary, ((convert(int, c1) | 2) & 2147475711))
490               where convert(smallint, substring(colidarray, 1, 2)) = @colid
491                   and id = object_id(@objname)
492                   and formatid = 100
493   
494               select @rows = @@rowcount, @err = @@error
495               if @err != 0
496               begin
497                   select @err
498                   rollback tran
499                   return (1)
500               end
501               if @rows >= 1
502               begin
503                   /* Successful update */
504                   commit tran
505                   return (0)
506               end
507               else
508               begin
509                   /* 18884: No statistics exist for column '%1!'. */
510                   raiserror 18884, @column_group
511                   rollback tran
512                   return (1)
513               end
514           end
515       end
516       /* end of REMOVE_STICKINESS module */
517   
518       else if (@option = "MODIFY_DENSITY" OR
519               @option = "MODIFY_DEFAULT_SELECTIVITY" OR
520               @option = "MODIFY_UNIQUE")
521       /* granular modification of density values, unique values and default selectivity values */
522       begin
523   
524           /*
525           ** The first action of any module is to setup
526           ** module specific variables and to validate
527           ** any module-specific usage.
528           */
529   
530           /* declare values local to this module */
531           declare @modify_type varchar(10) /* @opt_param1 */
532           declare @method varchar(8) /* @opt_param2 */
533           declare @target_value double precision /* @opt_param3 */
534           declare @col_count int, @col_length int
535           declare @col_position int, @position int
536           declare @colidarray varbinary(100)
537           declare @indid smallint
538           declare @partitionid int
539           declare @column_name varchar(255)
540           declare @column_string varchar(1200)
541           declare @modify_message varchar(255)
542           declare @modify_error int
543           declare @option_message varchar(1200)
544   
545           /* assign @opt_param values to semantically approriate values */
546           select @modify_type = @opt_param1, @method = @opt_param2,
547               @target_value = convert(double precision, @opt_param3)
548   
549           if (@option = "MODIFY_DENSITY" OR @option = "MODIFY_UNIQUE")
550           begin
551               select @modify_message = "range | total",
552                   @modify_error = case when @modify_type = "range" OR
553                           @modify_type = "total" then 1 else 0 end
554           end
555           else
556           begin
557               select @modify_message = "inequality | inbetween",
558                   @modify_error = case when @modify_type = "inequality" OR
559                           @modify_type = "inbetween" then 1 else 0 end
560           end
561   
562           /* Validate arguments*/
563           if (@column_group is null) or (@modify_error != 1)
564               or (@method not in ("absolute", "factor"))
565               or (@target_value is null)
566           begin
567               print "Usage for %1! option:", @option
568               print "sp_modifystats ""[database].[owner].table"", { ""column_group"" | ""all"" },"
569               print "  %1!, { %2! }, {absolute | factor}, ""value"" ", @option, @modify_message
570               return (1)
571           end
572   
573           /* Make sure our value isn't bogus */
574           if (@target_value < 0) or (@target_value > 1.0 and @method = "absolute")
575           begin
576               /*
577               ** 18922, "Command failed. The 'value' argument for 
578               ** 'MODIFY_DENSITY' must be greater than or equal to 0.0 and
579               ** not more than 1.0 when the 'absolute' method is specified."
580               */
581               raiserror 18922
582               return (1)
583           end
584   
585           /* Go through the column list looking for valid columns */
586           if @column_group != "all"
587           begin
588               select @col_length = char_length(@column_group)
589               select @position = 1
590               select @col_count = 0
591   
592               /* Eliminate possible white space between columns */
593               select @position = patindex("%,[ " + char(9) + "]%", @column_group)
594               while (@position != 0)
595               begin
596                   select @column_group =
597                       stuff(@column_group, @position + 1, 1, null)
598                   select @position = patindex("%,[ " + char(9) + "]%", @column_group)
599               end
600   
601               select @position = 1
602               select @col_length = char_length(@column_group)
603   
604               /*
605               ** We must loop through @column_group to find the column names.
606               ** @position is used in the outer loop, and marks the character
607               ** position within @column_group.  @col_position is used in
608               ** the inner loop and marks the number of characters FROM @position
609               ** until we hit either the end of the string or a comma.
610               */
611               while (@position <= @col_length)
612               begin
613                   select @col_position = 0
614                   while ((substring(@column_group,
615                                   (@position + @col_position), 1) != ",") and
616                           ((@position + @col_position) <= @col_length))
617                   begin
618                       select @col_position = @col_position + 1
619                   end
620   
621                   /* Check for wildcards */
622                   if (substring(@column_group, @position, @col_position) != "%")
623                   begin
624                       /* 
625                       ** Our column is not a wildcard, so make sure 
626                       ** it exits
627                       */
628                       if not exists (select colid from syscolumns
629                               where id = object_id(@objname)
630                                   and name like substring(@column_group,
631                                       @position, @col_position))
632                       begin
633                           select @column_name = substring(@column_group,
634                                   @position, @col_position)
635                           /*
636                           ** 17563: "The table does not have a
637                           ** column named '%1!'."
638                           */
639                           raiserror 17563, @column_name
640                           return (1)
641                       end
642                   end
643                   select @position = @position + @col_position + 1
644                   select @col_count = @col_count + 1
645               end
646               if (@col_count > 1 and @option = "MODIFY_DEFAULT_SELECTIVITY")
647               begin
648                   /* only single columns supported for default selectivity */
649                   raiserror 17563, @column_group
650                   return (1)
651               end
652           end /* column validation end */
653   
654           /* Create a #table to hold the old and new values */
655           create table #old_new_values(indid smallint, partitionid int,
656               colidarray varbinary(100),
657               column_string varchar(1200) null,
658               modify_type char(10),
659               old_value double precision null,
660               new_value double precision null)
661   
662           /* Create an index to aid in cursor processing */
663           create unique clustered index densities_CI on
664           #old_new_values(indid, partitionid, colidarray)
665   
666           /*
667           ** Insert into #old_new_values all formatid 100 rows for the object
668           ** we note the old value and set the new value to @target_value
669           */
670           insert #old_new_values select indid, partitionid, colidarray, null,
671               @modify_type,
672               case
673                   when (@modify_type = "range" and @option = "MODIFY_DENSITY") then convert(double precision, c2)
674                   when (@modify_type = "total" and @option = "MODIFY_DENSITY") then convert(double precision, c3)
675                   when (@modify_type = "range" and @option = "MODIFY_UNIQUE") then convert(double precision, c14)
676                   when (@modify_type = "total" and @option = "MODIFY_UNIQUE") then convert(double precision, c15)
677                   when @modify_type = "inequality" then convert(double precision, c10)
678                   when @modify_type = "inbetween" then convert(double precision, c11)
679               end
680               , @target_value
681           from sysstatistics where formatid = 100 and id = object_id(@objname) and
682               (@option != "MODIFY_DEFAULT_SELECTIVITY" or char_length(colidarray) <= 2)
683   
684           /* If we are using factoring, correct the new value */
685           if @method = "factor"
686               update #old_new_values set new_value = (old_value * @target_value)
687   
688           /* We now need to cursor through #old_new_values and rebuild  column string */
689           declare new_densities cursor for
690           select indid, partitionid, colidarray
691           from #old_new_values for update
692   
693           open new_densities
694           fetch new_densities into @indid, @partitionid, @colidarray
695           select @column_string = ""
696           while (@@sqlstatus = 0)
697           begin
698               select @position = 1
699               select @col_length = char_length(@colidarray)
700               /* Build the column list string */
701               while (convert(smallint, substring(@colidarray, @position, 2)) != 0)
702                   and (@position <= @col_length)
703               begin
704                   select @colid = convert(smallint,
705                       substring(@colidarray, @position, 2))
706                   if @position != 1
707                       select @column_string = @column_string + ","
708                   select @column_string = @column_string +
709                           (select name from syscolumns where
710                               id = object_id(@objname) and
711                               colid = @colid)
712                   select @position = @position + 2
713               end
714   
715               /* Now update the #table with the string */
716               update #old_new_values set column_string =
717                   ltrim(rtrim(@column_string))
718               where colidarray = @colidarray
719                   and indid = @indid
720                   and partitionid = @partitionid
721   
722               /* Get rid of the row if we don't want it */
723               if @column_group != "all"
724                   delete #old_new_values
725                   where column_string not like @column_group
726                       and colidarray = @colidarray
727                       and indid = @indid
728                       and partitionid = @partitionid
729   
730               /* Check to see it our row stills exists */
731               if exists (select * from #old_new_values
732                       where colidarray = @colidarray
733                           and indid = @indid
734                           and partitionid = @partitionid)
735               begin
736                   /*
737                   ** Now check to see if we have gone over 1.0 for
738                   ** a density
739                   */
740                   if (select new_value from #old_new_values
741                           where colidarray = @colidarray
742                               and indid = @indid
743                               and partitionid = @partitionid) > 1.0
744                   begin
745                       /* 
746                       ** 18923, "The density value of column group 
747                       ** '%1!' exceeded 1.0 and has been set to 1.0.
748                       ** Processing continues."
749                       */
750                       raiserror 18923, @column_string
751   
752                       update #old_new_values
753                       set new_value = 1.0
754                       where colidarray = @colidarray
755                           and indid = @indid
756                           and partitionid = @partitionid
757                   end
758               end
759               select @column_string = ""
760               fetch new_densities into @indid, @partitionid, @colidarray
761           end
762   
763           close new_densities
764           deallocate cursor new_densities
765   
766           /*
767           ** We can now set sysstatistics appropriately
768           ** Notice that c1 is or'd with 2, as this sets the stats edited flag
769           */
770           begin transaction
771           if (@option = "MODIFY_DENSITY")
772           begin
773               select @option_message = "Densities"
774   
775               if (@modify_type = "range")
776               begin /* Setting range density in this case */
777                   update sysstatistics set c2 = convert(varbinary(255), new_value),
778                       c1 = convert(varbinary, (convert(int, c1) | 2))
779                   from #old_new_values where
780                       sysstatistics.colidarray = #old_new_values.colidarray
781                       and formatid = 100 and id = object_id(@objname)
782                       and sysstatistics.indid = #old_new_values.indid
783                       and sysstatistics.partitionid = sysstatistics.partitionid
784               end
785               else
786               begin /* Setting total density in this case */
787                   update sysstatistics set c3 = convert(varbinary(255), new_value),
788                       c1 = convert(varbinary, (convert(int, c1) | 2))
789                   from #old_new_values where
790                       sysstatistics.colidarray = #old_new_values.colidarray
791                       and formatid = 100 and id = object_id(@objname)
792                       and sysstatistics.indid = #old_new_values.indid
793                       and sysstatistics.partitionid = sysstatistics.partitionid
794               end
795           end
796           else if (@option = "MODIFY_UNIQUE")
797           begin
798               select @option_message = "Uniqueness"
799   
800               if (@modify_type = "range")
801               begin /* Setting range unique in this case */
802                   update sysstatistics set c14 = convert(varbinary(255), new_value),
803                       c1 = convert(varbinary, (convert(int, c1) | 2))
804                   from #old_new_values where
805                       sysstatistics.colidarray = #old_new_values.colidarray
806                       and formatid = 100 and id = object_id(@objname)
807                       and sysstatistics.indid = #old_new_values.indid
808                       and sysstatistics.partitionid = sysstatistics.partitionid
809               end
810               else
811               begin /* Setting total unique in this case */
812                   update sysstatistics set c15 = convert(varbinary(255), new_value),
813                       c1 = convert(varbinary, (convert(int, c1) | 2))
814                   from #old_new_values where
815                       sysstatistics.colidarray = #old_new_values.colidarray
816                       and formatid = 100 and id = object_id(@objname)
817                       and sysstatistics.indid = #old_new_values.indid
818                       and sysstatistics.partitionid = sysstatistics.partitionid
819               end
820           end
821           else
822           begin
823               select @option_message = "Default Selectivities"
824   
825               if (@modify_type = "inequality")
826               begin /* Setting inequality in this case */
827                   update sysstatistics set c10 = convert(varbinary(255), new_value),
828                       c1 = convert(varbinary, (convert(int, c1) | 2))
829                   from #old_new_values where
830                       sysstatistics.colidarray = #old_new_values.colidarray
831                       and formatid = 100 and id = object_id(@objname)
832                       and sysstatistics.indid = #old_new_values.indid
833                       and sysstatistics.partitionid = sysstatistics.partitionid
834               end
835               else
836               begin /* Setting inbetween in this case */
837                   update sysstatistics set c11 = convert(varbinary(255), new_value),
838                       c1 = convert(varbinary, (convert(int, c1) | 2))
839                   from #old_new_values where
840                       sysstatistics.colidarray = #old_new_values.colidarray
841                       and formatid = 100 and id = object_id(@objname)
842                       and sysstatistics.indid = #old_new_values.indid
843                       and sysstatistics.partitionid = sysstatistics.partitionid
844               end
845           end
846   
847           /* Do some error checking */
848           select @rows = @@rowcount, @err = @@error
849           if @err != 0
850           begin
851               select @err
852               rollback tran
853               return (1)
854           end
855           if @rows >= 1
856           begin
857               /* Successful update */
858               commit tran
859           end
860           else
861           begin
862               /* 18884: No statistics exist for column '%1!'. */
863               raiserror 18884, @column_group
864               rollback tran
865               return (1)
866           end
867   
868           /*
869           ** Now display the output.  Pull new values from
870           ** sysstatistics for accuracy purposes
871           */
872   
873           select @option_message + " updated for table " + @objname + " by SP_MODIFYSTATS"
874   
875           if (@option = "MODIFY_DENSITY")
876           begin
877               select d.column_string "Column Group",
878                   d.modify_type "Density Type",
879                   convert(decimal(9, 8), d.old_value) "Original Value",
880                   case
881                       when @modify_type = "range" then
882                       convert(decimal(9, 8),
883                       convert(double precision, s.c2))
884                       when @modify_type = "total" then
885                       convert(decimal(9, 8),
886                       convert(double precision, s.c3))
887                   end "New Value"
888               from sysstatistics s, #old_new_values d where
889                   s.formatid = 100 and s.id = object_id(@objname) and
890                   s.colidarray = d.colidarray
891           end
892           else if (@option = "MODIFY_UNIQUE")
893           begin
894               select d.column_string "Column Group",
895                   d.modify_type "Unique Type",
896                   convert(decimal(9, 8), d.old_value) "Original Value",
897                   case
898                       when @modify_type = "range" then
899                       convert(decimal(9, 8),
900                       convert(double precision, s.c14))
901                       when @modify_type = "total" then
902                       convert(decimal(9, 8),
903                       convert(double precision, s.c15))
904                   end "New Value"
905               from sysstatistics s, #old_new_values d where
906                   s.formatid = 100 and s.id = object_id(@objname) and
907                   s.colidarray = d.colidarray
908           end
909           else
910           begin
911               select d.column_string "Column Group",
912                   d.modify_type "Selectivity Type",
913                   convert(decimal(9, 8), d.old_value) "Original Value",
914                   case
915                       when @modify_type = "inequality" then
916                       convert(decimal(9, 8),
917                       convert(double precision, s.c10))
918                       when @modify_type = "inbetween" then
919                       convert(decimal(9, 8),
920                       convert(double precision, s.c11))
921                   end "New Value"
922               from sysstatistics s, #old_new_values d where
923                   s.formatid = 100 and s.id = object_id(@objname) and
924                   s.colidarray = d.colidarray
925           end
926   
927           /* We are all done! */
928           return (0)
929   
930       end
931   


exec sp_procxmode 'sp_modifystats', 'AnyMode'
go
RESULT SETS
sp_modifystats_rset_008
sp_modifystats_rset_007
sp_modifystats_rset_006
sp_modifystats_rset_005
sp_modifystats_rset_004
sp_modifystats_rset_003
sp_modifystats_rset_002
sp_modifystats_rset_001

DEFECTS
 MCTR 4 Conditional Begin Tran or Commit Tran 397
 MCTR 4 Conditional Begin Tran or Commit Tran 417
 MCTR 4 Conditional Begin Tran or Commit Tran 437
 MCTR 4 Conditional Begin Tran or Commit Tran 461
 MCTR 4 Conditional Begin Tran or Commit Tran 478
 MCTR 4 Conditional Begin Tran or Commit Tran 504
 MCTR 4 Conditional Begin Tran or Commit Tran 770
 MCTR 4 Conditional Begin Tran or Commit Tran 858
 MEST 4 Empty String will be replaced by Single Space 695
 MEST 4 Empty String will be replaced by Single Space 759
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysprotects sybsystemprocs..sysprotects
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysstatistics sybsystemprocs..sysstatistics
 MTYP 4 Assignment type mismatch @method: varchar(8) = varchar(255) 546
 MTYP 4 Assignment type mismatch @modify_type: varchar(10) = varchar(255) 546
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysprotects.csysprotects unique clustered
(id, action, grantor, uid, protecttype, predid)
Intersection: {uid}
Uncovered: [grantor, protecttype, predid]
335
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysstatistics.csysstatistics unique clustered
(id, indid, partitionid, statid, colidarray, formatid, sequence)
Intersection: {colidarray, indid}
Uncovered: [partitionid, statid, sequence]
780
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysstatistics.csysstatistics unique clustered
(id, indid, partitionid, statid, colidarray, formatid, sequence)
Intersection: {colidarray, indid}
Uncovered: [partitionid, statid, sequence]
790
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysstatistics.csysstatistics unique clustered
(id, indid, partitionid, statid, colidarray, formatid, sequence)
Intersection: {colidarray, indid}
Uncovered: [partitionid, statid, sequence]
805
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysstatistics.csysstatistics unique clustered
(id, indid, partitionid, statid, colidarray, formatid, sequence)
Intersection: {colidarray, indid}
Uncovered: [partitionid, statid, sequence]
815
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysstatistics.csysstatistics unique clustered
(id, indid, partitionid, statid, colidarray, formatid, sequence)
Intersection: {colidarray, indid}
Uncovered: [partitionid, statid, sequence]
830
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysstatistics.csysstatistics unique clustered
(id, indid, partitionid, statid, colidarray, formatid, sequence)
Intersection: {colidarray, indid}
Uncovered: [partitionid, statid, sequence]
840
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: #old_new_values.densities_CI unique clustered
(indid, partitionid, colidarray)
Intersection: {colidarray}
Uncovered: [indid, partitionid]
890
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysstatistics.csysstatistics unique clustered
(id, indid, partitionid, statid, colidarray, formatid, sequence)
Intersection: {colidarray}
Uncovered: [indid, partitionid, statid, sequence]
890
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: #old_new_values.densities_CI unique clustered
(indid, partitionid, colidarray)
Intersection: {colidarray}
Uncovered: [indid, partitionid]
907
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysstatistics.csysstatistics unique clustered
(id, indid, partitionid, statid, colidarray, formatid, sequence)
Intersection: {colidarray}
Uncovered: [indid, partitionid, statid, sequence]
907
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: #old_new_values.densities_CI unique clustered
(indid, partitionid, colidarray)
Intersection: {colidarray}
Uncovered: [indid, partitionid]
924
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysstatistics.csysstatistics unique clustered
(id, indid, partitionid, statid, colidarray, formatid, sequence)
Intersection: {colidarray}
Uncovered: [indid, partitionid, statid, sequence]
924
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 280
 QTYP 4 Comparison type mismatch smallint = int 403
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 405
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 450
 QTYP 4 Comparison type mismatch smallint = int 490
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 492
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 681
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 711
 QTYP 4 Comparison type mismatch smallint = int 711
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 781
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 791
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 806
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 816
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 831
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 841
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 889
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 906
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 923
 CUSU 3 Cursor updated through 'searched update': risk of halloween rows new_densities 716
 CUSU 3 Cursor updated through 'searched update': risk of halloween rows new_densities 724
 CUSU 3 Cursor updated through 'searched update': risk of halloween rows new_densities 752
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 186
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 238
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 252
 MAW1 3 Warning message on %name% sybsystemprocs..sysprotects.id: Warning message on sysprotects 277
 MAW1 3 Warning message on %name% sybsystemprocs..sysroles.id: Warning message on sysroles 279
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 322
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 323
 MAW1 3 Warning message on %name% sybsystemprocs..sysprotects.id: Warning message on sysprotects 329
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 387
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 396
 MAW1 3 Warning message on %name% sybsystemprocs..sysstatistics.id: Warning message on sysstatistics 404
 MAW1 3 Warning message on %name% sybsystemprocs..sysstatistics.id: Warning message on sysstatistics 449
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 468
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 477
 MAW1 3 Warning message on %name% sybsystemprocs..sysstatistics.id: Warning message on sysstatistics 491
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 629
 MAW1 3 Warning message on %name% sybsystemprocs..sysstatistics.id: Warning message on sysstatistics 681
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 710
 MAW1 3 Warning message on %name% sybsystemprocs..sysstatistics.id: Warning message on sysstatistics 781
 MAW1 3 Warning message on %name% sybsystemprocs..sysstatistics.id: Warning message on sysstatistics 791
 MAW1 3 Warning message on %name% sybsystemprocs..sysstatistics.id: Warning message on sysstatistics 806
 MAW1 3 Warning message on %name% sybsystemprocs..sysstatistics.id: Warning message on sysstatistics 816
 MAW1 3 Warning message on %name% sybsystemprocs..sysstatistics.id: Warning message on sysstatistics 831
 MAW1 3 Warning message on %name% sybsystemprocs..sysstatistics.id: Warning message on sysstatistics 841
 MAW1 3 Warning message on %name% sybsystemprocs..sysstatistics.id: Warning message on sysstatistics 889
 MAW1 3 Warning message on %name% sybsystemprocs..sysstatistics.id: Warning message on sysstatistics 906
 MAW1 3 Warning message on %name% sybsystemprocs..sysstatistics.id: Warning message on sysstatistics 923
 MGTP 3 Grant to public master..syssrvroles  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..sysprotects  
 MGTP 3 Grant to public sybsystemprocs..sysroles  
 MGTP 3 Grant to public sybsystemprocs..sysstatistics  
 MGTP 3 Grant to public sybsystemprocs..sysusers  
 MNER 3 No Error Check should check @@error after insert 670
 MNER 3 No Error Check should check @@error after update 686
 MNER 3 No Error Check should check @@error after update 716
 MNER 3 No Error Check should check @@error after delete 724
 MNER 3 No Error Check should check @@error after update 752
 MNER 3 No Error Check should check @@error after update 777
 MNER 3 No Error Check should check @@error after update 787
 MNER 3 No Error Check should check @@error after update 802
 MNER 3 No Error Check should check @@error after update 812
 MNER 3 No Error Check should check @@error after update 827
 MNER 3 No Error Check should check @@error after update 837
 MUCO 3 Useless Code Useless Brackets 188
 MUCO 3 Useless Code Useless Brackets 196
 MUCO 3 Useless Code Useless Brackets 224
 MUCO 3 Useless Code Useless Brackets 233
 MUCO 3 Useless Code Useless Brackets 242
 MUCO 3 Useless Code Useless Brackets 251
 MUCO 3 Useless Code Useless Brackets 259
 MUCO 3 Useless Code Useless Brackets 286
 MUCO 3 Useless Code Useless Brackets 290
 MUCO 3 Useless Code Useless Brackets 317
 MUCO 3 Useless Code Useless Brackets 326
 MUCO 3 Useless Code Useless Brackets 327
 MUCO 3 Useless Code Useless Brackets 347
 MUCO 3 Useless Code Useless Brackets 349
 MUCO 3 Useless Code Useless Brackets 353
 MUCO 3 Useless Code Useless Brackets 355
 MUCO 3 Useless Code Useless Brackets 379
 MUCO 3 Useless Code Useless Brackets 383
 MUCO 3 Useless Code Useless Brackets 391
 MUCO 3 Useless Code Useless Brackets 412
 MUCO 3 Useless Code Useless Brackets 418
 MUCO 3 Useless Code Useless Brackets 425
 MUCO 3 Useless Code Useless Brackets 434
 MUCO 3 Useless Code Useless Brackets 457
 MUCO 3 Useless Code Useless Brackets 462
 MUCO 3 Useless Code Useless Brackets 472
 MUCO 3 Useless Code Useless Brackets 499
 MUCO 3 Useless Code Useless Brackets 505
 MUCO 3 Useless Code Useless Brackets 512
 MUCO 3 Useless Code Useless Brackets 518
 MUCO 3 Useless Code Useless Brackets 549
 MUCO 3 Useless Code Useless Brackets 570
 MUCO 3 Useless Code Useless Brackets 582
 MUCO 3 Useless Code Useless Brackets 594
 MUCO 3 Useless Code Useless Brackets 611
 MUCO 3 Useless Code Useless Brackets 614
 MUCO 3 Useless Code Useless Brackets 622
 MUCO 3 Useless Code Useless Brackets 640
 MUCO 3 Useless Code Useless Brackets 646
 MUCO 3 Useless Code Useless Brackets 650
 MUCO 3 Useless Code Useless Brackets 673
 MUCO 3 Useless Code Useless Brackets 674
 MUCO 3 Useless Code Useless Brackets 675
 MUCO 3 Useless Code Useless Brackets 676
 MUCO 3 Useless Code Useless Brackets 686
 MUCO 3 Useless Code Useless Brackets 696
 MUCO 3 Useless Code Useless Brackets 771
 MUCO 3 Useless Code Useless Brackets 775
 MUCO 3 Useless Code Useless Brackets 796
 MUCO 3 Useless Code Useless Brackets 800
 MUCO 3 Useless Code Useless Brackets 825
 MUCO 3 Useless Code Useless Brackets 853
 MUCO 3 Useless Code Useless Brackets 865
 MUCO 3 Useless Code Useless Brackets 875
 MUCO 3 Useless Code Useless Brackets 892
 MUCO 3 Useless Code Useless Brackets 928
 MUIN 3 Column created using implicit nullability 655
 QAFM 3 Var Assignment from potentially many rows 185
 QCRS 3 Conditional Result Set 410
 QCRS 3 Conditional Result Set 455
 QCRS 3 Conditional Result Set 497
 QCRS 3 Conditional Result Set 851
 QCRS 3 Conditional Result Set 873
 QCRS 3 Conditional Result Set 877
 QCRS 3 Conditional Result Set 894
 QCRS 3 Conditional Result Set 911
 QCTC 3 Conditional Table Creation 655
 QISO 3 Set isolation level 366
 QNAJ 3 Not using ANSI Inner Join 274
 QNAJ 3 Not using ANSI Inner Join 328
 QNAJ 3 Not using ANSI Inner Join 888
 QNAJ 3 Not using ANSI Inner Join 905
 QNAJ 3 Not using ANSI Inner Join 922
 QNAM 3 Select expression has no name @err 410
 QNAM 3 Select expression has no name @err 455
 QNAM 3 Select expression has no name @err 497
 QNAM 3 Select expression has no name @err 851
 QNAM 3 Select expression has no name @option_message + " updated for table " + @objname + " by SP_MODIFYSTATS" 873
 QNUA 3 Should use Alias: Column new_value should use alias #old_new_values 777
 QNUA 3 Should use Alias: Column c1 should use alias sysstatistics 778
 QNUA 3 Should use Alias: Table #old_new_values 779
 QNUA 3 Should use Alias: Column formatid should use alias sysstatistics 781
 QNUA 3 Should use Alias: Column id should use alias sysstatistics 781
 QNUA 3 Should use Alias: Column new_value should use alias #old_new_values 787
 QNUA 3 Should use Alias: Column c1 should use alias sysstatistics 788
 QNUA 3 Should use Alias: Table #old_new_values 789
 QNUA 3 Should use Alias: Column formatid should use alias sysstatistics 791
 QNUA 3 Should use Alias: Column id should use alias sysstatistics 791
 QNUA 3 Should use Alias: Column new_value should use alias #old_new_values 802
 QNUA 3 Should use Alias: Column c1 should use alias sysstatistics 803
 QNUA 3 Should use Alias: Table #old_new_values 804
 QNUA 3 Should use Alias: Column formatid should use alias sysstatistics 806
 QNUA 3 Should use Alias: Column id should use alias sysstatistics 806
 QNUA 3 Should use Alias: Column new_value should use alias #old_new_values 812
 QNUA 3 Should use Alias: Column c1 should use alias sysstatistics 813
 QNUA 3 Should use Alias: Table #old_new_values 814
 QNUA 3 Should use Alias: Column formatid should use alias sysstatistics 816
 QNUA 3 Should use Alias: Column id should use alias sysstatistics 816
 QNUA 3 Should use Alias: Column new_value should use alias #old_new_values 827
 QNUA 3 Should use Alias: Column c1 should use alias sysstatistics 828
 QNUA 3 Should use Alias: Table #old_new_values 829
 QNUA 3 Should use Alias: Column formatid should use alias sysstatistics 831
 QNUA 3 Should use Alias: Column id should use alias sysstatistics 831
 QNUA 3 Should use Alias: Column new_value should use alias #old_new_values 837
 QNUA 3 Should use Alias: Column c1 should use alias sysstatistics 838
 QNUA 3 Should use Alias: Table #old_new_values 839
 QNUA 3 Should use Alias: Column formatid should use alias sysstatistics 841
 QNUA 3 Should use Alias: Column id should use alias sysstatistics 841
 QPNC 3 No column in condition 682
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
186
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysprotects.csysprotects unique clustered
(id, action, grantor, uid, protecttype, predid)
Intersection: {action, id}
277
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
387
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
396
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysstatistics.csysstatistics unique clustered
(id, indid, partitionid, statid, colidarray, formatid, sequence)
Intersection: {colidarray, id, formatid}
403
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysstatistics.csysstatistics unique clustered
(id, indid, partitionid, statid, colidarray, formatid, sequence)
Intersection: {formatid, id}
449
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
468
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
477
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysstatistics.csysstatistics unique clustered
(id, indid, partitionid, statid, colidarray, formatid, sequence)
Intersection: {colidarray, id, formatid}
490
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
629
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysstatistics.csysstatistics unique clustered
(id, indid, partitionid, statid, colidarray, formatid, sequence)
Intersection: {colidarray, id, formatid}
681
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {colid, id}
710
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: #old_new_values.densities_CI unique clustered
(indid, partitionid, colidarray)
Intersection: {colidarray, indid}
Uncovered: [partitionid]
780
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: #old_new_values.densities_CI unique clustered
(indid, partitionid, colidarray)
Intersection: {colidarray, indid}
Uncovered: [partitionid]
790
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: #old_new_values.densities_CI unique clustered
(indid, partitionid, colidarray)
Intersection: {colidarray, indid}
Uncovered: [partitionid]
805
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: #old_new_values.densities_CI unique clustered
(indid, partitionid, colidarray)
Intersection: {colidarray, indid}
Uncovered: [partitionid]
815
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: #old_new_values.densities_CI unique clustered
(indid, partitionid, colidarray)
Intersection: {colidarray, indid}
Uncovered: [partitionid]
830
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: #old_new_values.densities_CI unique clustered
(indid, partitionid, colidarray)
Intersection: {colidarray, indid}
Uncovered: [partitionid]
840
 QPSC 3 Join on same column sysstatistics.partitionid 783
 QPSC 3 Join on same column sysstatistics.partitionid 793
 QPSC 3 Join on same column sysstatistics.partitionid 808
 QPSC 3 Join on same column sysstatistics.partitionid 818
 QPSC 3 Join on same column sysstatistics.partitionid 833
 QPSC 3 Join on same column sysstatistics.partitionid 843
 VNRD 3 Variable is not read @nullarg 314
 VNRD 3 Variable is not read @dummy 358
 CRDO 2 Read Only Cursor Marker (has for read only clause) 274
 CUPD 2 Updatable Cursor Marker (has for update clause) 690
 MRST 2 Result Set Marker 410
 MRST 2 Result Set Marker 455
 MRST 2 Result Set Marker 497
 MRST 2 Result Set Marker 851
 MRST 2 Result Set Marker 873
 MRST 2 Result Set Marker 877
 MRST 2 Result Set Marker 894
 MRST 2 Result Set Marker 911
 MSUB 2 Subquery Marker 237
 MSUB 2 Subquery Marker 251
 MSUB 2 Subquery Marker 322
 MSUB 2 Subquery Marker 326
 MSUB 2 Subquery Marker 386
 MSUB 2 Subquery Marker 395
 MSUB 2 Subquery Marker 467
 MSUB 2 Subquery Marker 476
 MSUB 2 Subquery Marker 628
 MSUB 2 Subquery Marker 709
 MSUB 2 Subquery Marker 731
 MSUB 2 Subquery Marker 740
 MTR1 2 Metrics: Comments Ratio Comments: 43% 25
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 68 = 91dec - 25exi + 2 25
 MTR3 2 Metrics: Query Complexity Complexity: 452 25
 PRED_QUERY_COLLECTION 2 {p=sybsystemprocs..sysprotects, r=sybsystemprocs..sysroles, s=master..syssrvroles} 0 274
 PRED_QUERY_COLLECTION 2 {p=sybsystemprocs..sysprotects, u=sybsystemprocs..sysusers} 0 326

DATA PROPAGATION detailed
ColumnWritten To
@objnamesp_modifystats_rset_004.sqlbNoName61
@opt_param3sysstatistics.c2   °.c3   °.c10   °.c11   °.c14   °.c15  

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysprotects  
read_writes table sybsystemprocs..sysstatistics  
reads table master..syssrvroles (1)  
writes table sybsystemprocs..sp_modifystats_rset_006 
writes table sybsystemprocs..sp_modifystats_rset_002 
writes table sybsystemprocs..sp_modifystats_rset_004 
writes table sybsystemprocs..sp_modifystats_rset_007 
reads table sybsystemprocs..sysroles  
reads table sybsystemprocs..syscolumns  
reads table sybsystemprocs..sysusers  
writes table sybsystemprocs..sp_modifystats_rset_008 
writes table sybsystemprocs..sp_modifystats_rset_003 
writes table sybsystemprocs..sp_modifystats_rset_005 
writes table sybsystemprocs..sp_modifystats_rset_001 
read_writes table tempdb..#old_new_values (1) 
reads table sybsystemprocs..sysobjects  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)