DatabaseProcApplicationCreatedLinks
sybsystemprocssp_chgattribute  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "@(#) generic/sproc/chgattribute 1.1 4/6/95 " */
3     /*
4     ** Generated by spgenmsgs.pl on Tue May 31 02:59:14 2005 
5     */
6     /*
7     ** raiserror Messages for /calm/svr/sql/generic/sproc/chgattribute [Total 19]
8     **
9     ** 17260, "Can't run %1! from within a transaction."
10    ** 17460, "Object must be in the current database."
11    ** 17756, "The execution of the stored procedure '%1!' in database '%2!' was aborted because there was an error in writing the replication log record."
12    ** 17760, "%1!' is a system table. Cannot use '%2!' on system tables."
13    ** 17782, "You do not own a table, column or index of that name in the current database."
14    ** 18121, "Unrecognized change attribute option."
15    ** 18571, "The attribute '%1!' is not applicable to tables with allpages lock scheme."
16    ** 18572, "The value for attribute '%1!' must be between %2! and %3!."
17    ** 18573, "Failed to update attribute '%1!' for object '%2!'."
18    ** 18612, "Invalid value specified for option '%1!'. Valid range of values is %2! to %3!."
19    ** 18613, "The attribute '%1!' is applicable to tables only."
20    ** 18840, "The value specified for identity gap %1! is not legal; identity gap must be greater than or equal to 0."
21    ** 18983, "The '%1!' attribute is not applicable to tables with datarow or datapage lock schemes."
22    ** 18985, "The value for '%1!' attribute must be between '%2!' and '%3!'."
23    ** 18987, "The '%1!' attribute could not be set to the specified value for the object."
24    ** 19115, "A value must be specified for attribute '%1!'."
25    ** 19116, "Table '%1!' does not have an identity column."
26    ** 19117, "The value %1! for '%2!' attribute must be greater than or equal to the current maximum identity value %3!."
27    ** 19118, "Object '%1!' is currently being accessed by other users. Failed to update attribute '%2!'."
28    */
29    /*
30    ** sp_getmessage Messages for /calm/svr/sql/generic/sproc/chgattribute [Total 1]
31    **
32    ** 19573, "sp_chgattribute is not allowed for %1!, as it is a virtually hashed table."
33    ** 18122, "'%1!' attribute of object '%2!' changed to %3!."
34    */
35    /*
36    ** End spgenmsgs.pl output.
37    */
38    
39    /*
40    ** For the option dealloc_first_txtpg we use the internal procedure sp_optimal_text_space
41    ** to perform the necessary checks/updates. This procedure could raise additional
42    ** messages.
43    */
44    
45    create or replace procedure sp_chgattribute
46        @objname varchar(767), /* table or index name */
47        @optname varchar(30),
48        @optvalue int,
49        @optvalue2 varchar(38) = NULL /* Current max digits for numeric */
50    as
51    
52        declare @objid int /* object id of the table */
53        declare @indid smallint /* table's index id  */
54        declare @msg varchar(1024)
55        declare @dbname varchar(255)
56        declare @dbid int /* database id */
57        declare @tabname varchar(255) /* name of table */
58        declare @indname varchar(255) /* name of index */
59        declare @varcol_count smallint /* Number of variable length columns */
60        declare @sysstat2 int /* status bits of the table */
61            , @sysstat int /* objstat from sysobjects*/
62            , @objtype varchar(2) /* Object's type */
63        declare @is_table_APL int /* flag to distinguish APL and DOl tables */
64        declare @dbcc_arg_3 smallint /* third argument to dbcc command. 
65        ** This must be of type smallint.
66        */
67        declare @newoptvalue varchar(10) /* Used for converting the optvalue */
68        declare @opt_ind_lock int /* server status for optimistic index lock */
69        declare @opt_cached_ind_root int /* object status for cached index root page */
70        declare @ptn_locking int /* object status for enabling partition lock */
71        declare @enable int /* if enable */
72        declare @ind_unique_localidx smallint /* server status for unique local index */
73        declare @curlimit16 smallint /* current value for int16 column */
74        declare @newlimit16 smallint /* new value for int16 column */
75        declare @curlimit32 int /* current value for int32 column */
76        declare @newlimit32 int /* new value for int32 column */
77        declare @dbcc_arg_4 int /* forth argument to dbcc command. 
78        ** This must be of type int.
79        */
80        declare @retstatus int
81        declare @curr_max_idtval numeric(38, 0) /* current max id value in the table. */
82        declare @new_idtval numeric(38, 0) /* numeric number of input optvalue2 */
83        declare @colname varchar(255) /* name of identity column */
84        declare @sqltext varchar(600) /* tmp holder for sqltext */
85        declare @tmp_indid int /* tmp indid holder */
86        declare @upd_rowcnt int /* updated row count */
87        declare @hash_bit int
88    
89        /* If we're in a transaction, disallow this */
90        if @@trancount > 0
91        begin
92            /*
93            ** 17260, "Can't run %1! from within a transaction."
94            */
95            raiserror 17260, "sp_chgattribute"
96            return (1)
97        end
98        else
99        begin
100           set chained off
101       end
102   
103       set transaction isolation level 1
104   
105       if @objname like "%.%.%"
106       begin
107           /*
108           ** 17460, "Object must be in the current database."
109           */
110           raiserror 17460
111           return (1)
112       end
113   
114       if not exists (
115               select * from master.dbo.spt_values
116               where type = "H"
117                   and name = @optname)
118       begin
119           /*
120           ** 18121, "Unrecognized change attribute option."
121           */
122           raiserror 18121
123           return (1)
124       end
125   
126       if @objname like "%.%"
127       begin
128           /*
129           ** Attributes 'exp_row_size', 'concurrency_opt_threshold' and 
130           ** 'ascinserts' apply only to a table and not to an index
131           */
132           if (@optname = "concurrency_opt_threshold" or
133                   @optname = "exp_row_size" or
134                   @optname = "optimistic_index_lock" or
135                   @optname = "cached_index_root_page" or
136                   @optname = "ascinserts")
137           begin
138               /*
139               ** 18613, "The attribute '%1!' is applicable to tables only."
140               */
141               raiserror 18613, @optname
142               return (1)
143           end
144   
145           /*
146           **  Get the table name and index name out.
147           */
148           select @tabname = substring(@objname, 1, charindex(".", @objname) - 1)
149           select @indname = substring(@objname, charindex(".", @objname) + 1, 511)
150   
151           select @objid = si.id, @indid = si.indid, @objtype = so.type
152           from sysobjects so, sysindexes si
153           where si.id = so.id
154               and so.name = @tabname
155               and si.name = @indname
156               and si.indid != 0
157               and so.type in ('U', 'S') -- user / system tables
158               and so.uid = user_id()
159       end
160       else
161       begin
162           select @tabname = @objname
163           select @objid = si.id, @indid = si.indid, @objtype = so.type
164           from sysobjects so, sysindexes si
165           where so.name = @tabname
166               and si.id = so.id
167               and si.indid in (0, 1)
168               and so.type in ('U', 'S') -- user / system tables
169               and so.uid = user_id()
170       end
171       /*
172       **  If @objid is still NULL then that means it isn't a 
173       **  table/index name.  
174       */
175       if (@objid is null)
176       begin
177           /*
178           ** 17782, "You do not own a table, column or index of that name in 
179           ** the current database."
180           */
181           raiserror 17782
182           return (1)
183       end
184   
185       /*
186       ** Determine whether the table we are working is APL or DOL.
187       **
188       ** The bits 0x2000 (8192) and 0x0 (0) represent allpages
189       ** lock scheme. The value of 0 indicates that no lock
190       ** scheme is specified (old style tables), so they only
191       ** support allpages locking.
192       **
193       ** The bits 0x4000 (16384) and 0x8000 (32768) represent
194       ** DOL tables respectively datapages only and datarows
195       ** lock schemes.
196       **
197       ** The value of 57344 is 8192+16384+32768.
198       **
199       ** sysstat: 0x400 (1024) is for fake tables.
200       */
201       select @sysstat2 = (sysstat2 & 57344)
202           , @sysstat = (sysstat & 1024) -- Eliminate fake catalogs
203       from sysobjects
204       where id = @objid
205   
206       if (@sysstat2 = 8192 or @sysstat2 = 0)
207           select @is_table_APL = 1
208       else
209           select @is_table_APL = 0
210   
211       if (@sysstat = 1024)
212       begin
213           /*
214           ** Do not allow executing this for fake system catalogs.
215           ** It's meaningless and might cause unnecessary side-effects.
216           ** 18613, "The attribute '%1!' is applicable to tables only."
217           */
218           raiserror 18613, @optname
219           return (2)
220       end
221   
222       /*
223       ** Restrict use of this interface on system catalogs to only those
224       ** options that control space-management properties. We don't want
225       ** users to accidentally define, say, concurrency_opt_threshold 
226       ** and cause unforeseen locking behaviour changes for system
227       ** catalogs.
228       */
229       if ((@objtype = 'S')
230               and @optname NOT IN ("concurrency_opt_threshold", "exp_row_size"))
231       begin
232   
233           select @msg = "sp_chgattribute " + @optname
234           raiserror 17760, @tabname, @msg
235           return (1)
236       end
237   
238   
239       /* 
240       ** In preparation for the system catalogs and in-memory cache updates
241       ** check each option for the valid option specifications and raise 
242       ** errors if necessary. 
243       **
244       ** option			catalog		column 		size
245       ** ------------------------------------------------------------------
246       ** concurrency_opt_threshold	systabstats	conopt_thld	int16
247       ** plldegree			systabstats	plldegree	int16
248       ** identity_gap			sysindexes	identitygap	int32
249       ** reservepagegap		sysindexes	res_page_gap	int16
250       ** fillfactor			sysindexes	fill_factor	int16
251       ** max_rows_per_page		sysindexes	maxrowsperpage	int16
252       ** exp_row_size			sysindexes	exp_rowsize	int16
253       ** optimistic_index_lock	sysobjects	sysstat2	int32
254       ** dealloc_first_txtpg		sysobjects	sysstat2	int32
255       ** local_unique_index           sysindexes      status3         int16
256       ** cached_index_root_page	sysobjects	sysstat4	int32
257       **
258       ** option			domain validity checks made herein
259       **				for @optvalue
260       ** -----------------------------------------------------------------------
261       ** concurrency_opt_threshold	>= -1 and <= 32767
262       ** plldegree			>= -1 and <= 32767
263       ** identity_gap			>= 0
264       ** reservepagegap		>= 0 and <= 255
265       ** fillfactor			>= 0 and <= 100
266       ** max_rows_per_page		none, instead via dbcc chgindcachedvalue()
267       ** exp_row_size			none, instead via dbcc chgindcachedvalue()
268       ** optimistic_index_lock	0,1 (mode off / mode on)
269       ** dealloc_first_txtpg		0,1,2 or NULL.
270       ** ptn_locking			0,1 (mode off / mode on)
271       ** identity_burn_max		none, instead via dbcc set_identity_burn_max()
272       ** local_unique_index           0,1 (mode off / mode on) 
273       ** ascinserts			0,1 (mode off / mode on)
274       ** cached_index_root_page	0,1 (mode off / mode on)
275       **
276       ** When a domain check passes we assign @optvalue to an int16 or int32
277       ** local variable respectively @newlimit16 or @newlimit32.  If the new
278       ** limit and the current value (@curlimit16 or @curlimit32) are the same
279       ** we consider this sproc invocation being a NOOP.
280       **
281       ** When the above checks pass below is what we do for the options :
282       **
283       **	reservepagegap,
284       **	max_rows_per_page,
285       **	fillfactor,
286       **	identity_gap,
287       **	concurrency_opt_threshold and
288       **	dealloc_first_txtpg.
289       **
290       ** 1)	We start transaction rs_logexec
291       **
292       ** 2)	We update sysindexes catalog and the in-memory cache thru
293       **	dbcc chgindcachedvalue().
294       **
295       **	We do NOT update sysindexes for option exp_row_size since
296       **	dbcc chgindcachedvalue() will begin a xact, update sysindexes
297       **	and then refresh the in-memory cache.  Notice that during the
298       **	checks phase for this option we may need to update sysindexes
299       **	if the column had a null value and this MUST not be done under
300       **	a transaction (here rs_logexec).  See comment further down for
301       **	the reason.  For that same reason for this option we only want
302       **	to execute:
303       **		begin tran rs_logexec
304       **		dbcc chgindcachedvalue("exp_row_size",...)
305       **		commit tran
306       **
307       **	For concurrency_opt_threshold, we update systabstats catalog and
308       **	the in-memory cache in pdes thru dbcc chgindcachedvalue().
309       **
310       ** 3)	We logexec() this sproc replication execution.
311       **
312       ** 4)	We commit transaction rs_logexec
313       **
314       **	For the optimistic_index_lock option we update sysobjects and
315       **	(un)set the status 02_OPT_INDEX_LOCK in sysstat2. The command
316       **	dbcc tune() is used to update the in cache structures. The
317       **	exec of the stored procedure sp_chgattribute for this option
318       **	is not replicated on purpose. Doing this could seriously
319       **	impact performance on the replicated server if the workload on
320       **	the replicated server does not warrant this property.
321       **
322       **	For the dealloc_first_txtpg option we update sysobjects and
323       **	(un)set the status 02_DEALLOC_FIRST_TXTPG in sysstat2. To
324       **	handle all the additional logic for this option we call an
325       **	internal stored procedure sp_optimal_text_space. The return
326       **	status is checked. 
327       **
328       ** NOTE: All updates to sysindexes are done by forcing the index access via
329       ** 'csysindexes' so that it's clear that only one row is being updated.
330       ** (Multi-row updates to sysindexes are prohibited.)
331       */
332   
333       /*
334       **	For virtually hashed tables only options which are
335       **	allowed are "optimistic_index_lock" and "identitygap".
336       */
337       select @hash_bit = number from master.dbo.spt_values
338       where name = "virtually hashed table" and type = "O2"
339       if exists (select * from sysobjects
340               where id = object_id(@tabname) and @hash_bit = sysstat2 & @hash_bit)
341       begin
342           /*
343           ** 19573, "sp_chgattribute is not allowed for %1!,
344           as it is a virtually hashed table."
345           */
346           raiserror 19573, @tabname
347           return (1)
348       end
349   
350       if (@optname = "dealloc_first_txtpg")
351       begin
352           exec @retstatus = sp_optimal_text_space @objname,
353               @optname,
354               @optvalue
355   
356           if (@retstatus != 0)
357           begin
358               /*
359               ** No need to raise an error, this is already
360               ** done by sp_optimal_text_space.
361               */
362               return (@retstatus)
363           end
364       end
365   
366       if (@optname = "optimistic_index_lock")
367       begin
368           select @dbname = db_name()
369   
370           select @dbid = dbid from master.dbo.sysdatabases
371           where name = @dbname
372   
373           /* 
374           ** This option is only applicable to APL tables.
375           */
376           if (@is_table_APL = 0)
377           begin
378               /*
379               ** 18983, "The attribute '%1!' is not applicable
380               **         to tables with datarow or datapages lock scheme."
381               */
382               raiserror 18983, @optname
383               return (1)
384           end
385   
386           if (@optvalue not in (0, 1))
387           begin
388               /*
389               ** 18985, "The value for attribute '%1!' must be
390               **         between '%2!' and '%3!'"
391               */
392               raiserror 18985, @optname, 0, 1
393               return (1)
394           end
395   
396           /* server defined constant for optimistic index lock */
397           select @opt_ind_lock = 268435456
398   
399           begin transaction chg_opt_ind_lock
400   
401           if (@optvalue = 1)
402           begin
403               update sysobjects
404               set sysstat2 = sysstat2 | @opt_ind_lock
405               where name = @objname
406           end
407           else
408           begin
409               update sysobjects
410               set sysstat2 = sysstat2 & ~ @opt_ind_lock
411               where name = @objname
412           end
413   
414           if (@@error != 0)
415           begin
416               rollback tran
417   
418               /*
419               ** "The attribute specified '%1!' could 
420               **  not be set for the object. "
421               */
422               raiserror 18987, @optname
423   
424               return (1)
425           end
426   
427           select @newoptvalue = convert(varchar(10), @optvalue)
428   
429           dbcc tune(@optname, @dbid, @objname, @newoptvalue)
430   
431           if (@@error != 0)
432           begin
433               rollback tran
434   
435               /*
436               ** "The attribute specified '%1!' could 
437               **  not be set for the object. "
438               */
439               raiserror 18987, @optname
440               return (1)
441           end
442   
443           commit transaction
444   
445           /*
446           ** We don't want the exec of sp_chgattribute to
447           ** be replicated for this option (see comment above
448           ** where we comment each option).
449           */
450           goto proc_end
451       end
452       else if (@optname = "cached_index_root_page")
453       begin
454           select @dbname = db_name()
455   
456           select @dbid = dbid from master.dbo.sysdatabases
457           where name = @dbname
458   
459           if (@is_table_APL = 1)
460           begin
461               /*
462               ** 18571, "The attribute '%1!' is not applicable
463               ** to tables with allpages lock scheme."
464               */
465               raiserror 18571, @optname
466               return (1)
467           end
468   
469           if (@optvalue not in (0, 1))
470           begin
471               /*
472               ** 18985, "The value for '%1!' attribute must be
473               ** between '%2!' and '%3!'."
474               */
475               raiserror 18985, @optname, 0, 1
476               return (1)
477           end
478   
479           /* server defined constant for cached dol index root page */
480           select @opt_cached_ind_root = 64
481   
482           begin transaction chg_cached_ind_root
483   
484           if (@optvalue = 1)
485           begin
486               update sysobjects
487               set sysstat4 = sysstat4 | @opt_cached_ind_root
488               where name = @objname
489           end
490           else
491           begin
492               update sysobjects
493               set sysstat4 = sysstat4 & ~ @opt_cached_ind_root
494               where name = @objname
495           end
496   
497           if (@@error != 0)
498           begin
499               rollback tran
500   
501               /*
502               ** 18987, "The attribute specified '%1!' could
503               ** not be set for the object. "
504               */
505               raiserror 18987, @optname
506               return (1)
507           end
508   
509           select @newoptvalue = convert(varchar(10), @optvalue)
510   
511           dbcc tune(@optname, @dbid, @objname, @newoptvalue)
512   
513           if (@@error != 0)
514           begin
515               rollback tran
516   
517               /*
518               ** 18987, "The attribute specified '%1!' could
519               ** not be set for the object. "
520               */
521               raiserror 18987, @optname
522               return (1)
523           end
524   
525           commit transaction
526   
527           goto proc_end
528   
529       end
530   
531       if (@optname = "local_unique_index")
532       begin
533           select @dbname = db_name()
534   
535           select @dbid = dbid from master.dbo.sysdatabases
536           where name = @dbname
537   
538   
539           if (@optvalue not in (0, 1))
540           begin
541               /*
542               ** 18985, "The value for attribute '%1!' must be
543               **         between '%2!' and '%3!'"
544               */
545               raiserror 18985, @optname, 0, 1
546               return (1)
547           end
548   
549           /* server defined constant for optimistic index lock */
550           select @ind_unique_localidx = 64
551   
552           select @tmp_indid = indid from sysindexes
553           where id = object_id(@objname) and indid < 2
554   
555           begin transaction chg_unique_ind_lock
556   
557           if (@optvalue = 1)
558           begin
559               select @dbcc_arg_3 = 1
560   
561               update sysindexes
562               set status3 = status3 | @ind_unique_localidx
563               where id = object_id(@objname) and indid = @tmp_indid
564                   and (status3 & @ind_unique_localidx) = 0
565               plan "(update (i_scan csysindexes sysindexes))"
566           end
567           else
568           begin
569               select @dbcc_arg_3 = 0
570   
571               update sysindexes
572               set status3 = status3 & ~ @ind_unique_localidx
573               where id = object_id(@objname) and indid = @tmp_indid
574                   and (status3 & @ind_unique_localidx) != 0
575               plan "(update (i_scan csysindexes sysindexes))"
576           end
577   
578           select @upd_rowcnt = @@rowcount
579   
580           if (@@error != 0)
581           begin
582               rollback tran
583   
584               /*
585               ** "The attribute specified '%1!' could
586               **  not be set for the object. "
587               */
588               raiserror 18987, @optname
589               return (1)
590           end
591   
592           if (@upd_rowcnt != 0)
593           begin
594               select @dbcc_arg_4 = 0
595   
596               dbcc chgindcachedvalue(@optname, @objname, @dbcc_arg_3, @dbcc_arg_4)
597           end
598   
599           if (@@error != 0)
600           begin
601               rollback tran
602   
603               /*
604               ** "The attribute specified '%1!' could
605               **  not be set for the object. "
606               */
607               raiserror 18987, @optname
608   
609               return (1)
610           end
611   
612           commit transaction
613       end
614   
615       if (@optname = "ascinserts")
616       begin
617           /*
618           ** This option is only applicable to APL tables.
619           */
620           if (@is_table_APL = 0)
621           begin
622               /*
623               ** 18983, "The attribute '%1!' is not applicable
624               **         to tables with datarow or datapages lock scheme.
625               */
626               raiserror 18983, @optname
627               return (1)
628           end
629   
630           if (@optvalue not in (0, 1))
631           begin
632               /*
633               ** 18985, "The value for attribute '%1!' must be
634               **         between '%2!' and '%3!'"
635               */
636               raiserror 18985, @optname, 0, 1
637               return (1)
638           end
639   
640           dbcc tune(@optname, @optvalue, @objname)
641   
642           if (@@error != 0)
643           begin
644               /*
645               ** "The attribute specified '%1!' could
646               ** not be set for the object."
647               */
648               raiserror 18987, @optname
649               return (1)
650           end
651   
652           goto proc_end
653       end
654   
655   
656   
657       /* 
658       ** From here the third argument is set to the indexid.
659       */
660       select @dbcc_arg_3 = @indid
661   
662       if @optname = "concurrency_opt_threshold"
663       begin
664           /* 
665           ** This option is only applicable to DOL tables.
666           */
667           if (@is_table_APL = 1)
668           begin
669               /*
670               ** 18571, "The attribute '%1!' is not applicable
671               **         to tables with allpages lock scheme."
672               */
673               raiserror 18571, @optname
674               return (1)
675           end
676   
677           if (@optvalue < - 1 or @optvalue > 32767 or @optvalue is null)
678           begin
679               /*
680               ** 18612, "Invalid value specified for option '%1!'. 
681               **         Valid range of values is %2! to %3!."
682               */
683               raiserror 18612, @optname, - 1, 32767
684               return (1)
685           end
686   
687           select @newlimit16 = convert(smallint, @optvalue)
688   
689           /*
690           ** Do nothing if the new specified and current values are the same.
691           */
692           select @curlimit16 = conopt_thld
693           from systabstats
694           where id = @objid and indid = 0
695   
696           if (@curlimit16 = @newlimit16)
697               goto proc_end
698       end
699       else if @optname = "plldegree"
700       begin
701           if (@optvalue < - 1 or @optvalue > 32767 or @optvalue is null)
702           begin
703               /*
704               ** 18612, "Invalid value specified for option '%1!'. 
705               **         Valid range of values is %2! to %3!."
706               */
707               raiserror 18612, @optname, - 1, 32767
708               return (1)
709           end
710   
711           select @newlimit16 = convert(smallint, @optvalue)
712   
713           /*
714           ** Do nothing if the new specified and current values are the same.
715           */
716           select @curlimit16 = plldegree
717           from systabstats
718           where id = @objid and indid = @indid
719   
720           if (@curlimit16 = @newlimit16)
721               goto proc_end
722       end
723       else if @optname = "reservepagegap"
724       begin
725           /* 
726           ** Change required in the reservepagegap value of the table/index. 
727           ** The third argument of dbcc chgindcachedvalue() is index id.
728           ** The maximum value for reservepagegap set to 255. The only
729           ** reason for choosing this limit is that number of pages in 
730           ** an allocation unit is 256. 
731           */
732           if (@optvalue < 0 or @optvalue > 255 or @optvalue is null)
733           begin
734               /*
735               ** 18572, "The value for attribute '%1!' must be
736               **         between %2! and %3!."
737               */
738               raiserror 18572, @optname, 0, 255
739               return (1)
740           end
741   
742           select @newlimit16 = convert(smallint, @optvalue)
743   
744           /*
745           ** Do nothing if the new specified and current values are the same.
746           */
747           select @curlimit16 = res_page_gap
748           from sysindexes
749           where id = @objid and indid = @indid
750   
751           if (@curlimit16 = @newlimit16)
752               goto proc_end
753       end
754       else if @optname = "identity_gap"
755       begin
756           /* 
757           ** Raise error if negative value is specified for identity gap.
758           */
759           if (@optvalue < 0 or @optvalue is null)
760           begin
761               /* 
762               ** 18840, "The value specified for identity gap %1! is not
763               **         legal; identity gap must be greater than or equal
764               **         to 0."
765               */
766               raiserror 18840, @optvalue
767               return (1)
768           end
769   
770           select @newlimit32 = @optvalue
771   
772           /*
773           ** Do nothing if the new specified and current values are the same.
774           */
775           select @curlimit32 = identitygap
776           from sysindexes
777           where id = @objid and indid = @indid
778   
779           if (@curlimit32 = @newlimit32)
780               goto proc_end
781       end
782       else if @optname = "exp_row_size"
783       begin
784           /*
785           ** Domain validity checks are done by dbcc chgindcachedvalue().
786           */
787           select @newlimit16 = convert(smallint, @optvalue)
788   
789           /*
790           ** Do nothing if the new specified and current values are the same.
791           */
792           select @curlimit16 = exp_rowsize
793           from sysindexes
794           where id = @objid and indid = @indid
795   
796           if (@curlimit16 = @newlimit16)
797               goto proc_end
798   
799           /* 
800           ** Set the exp_row_size value of the table/index. 
801           **
802           ** The actual value set in sysindexes table for this table must
803           ** include the row overhead, which depends on the number of variable
804           ** length columns in the table. Since this attribute is applicable 
805           ** only for tables (not indexes) we are using the third argument 
806           ** of dbcc chgindcachedvalue() to pass the the number of variable 
807           ** length columns in the table. For this atribute, the sysindexes 
808           ** table will be updated by dbcc chgindcachedvalue(). This is because 
809           ** it is easier to calculate the overhead in the server than doing 
810           ** it here.
811           **
812           ** Get the number of variable length columns the table has. 
813           ** This is determined by counting the number of columns that have
814           ** offset < 0. If a column is nullable, its offset will be < 0.
815           **
816           ** This is required to determine the row overhead. 
817           */
818           select @varcol_count = count(*)
819           from syscolumns
820           where id = @objid and offset < 0
821   
822           select @dbcc_arg_3 = @varcol_count
823   
824           /*
825           ** Update sysindexes and set the exp_row_size value to 0
826           ** if the column still has a null value. This is to facilitate
827           ** update to exp_row_size through sysindexes manager later
828           ** (thru dbcc chgindcachedvalue). This can be removed if we
829           ** are sure that exp_row_size can never be null. An important
830           ** point to note is this update is _not_ done within the scope
831           ** of the transaction which calls dbcc chgindcachedvalue and
832           ** will not rollback in the event of any failure after this
833           ** transaction. We don't want to call dbcc chgindcachedvalue
834           ** in the same transaction as this update, due to some in-memory
835           ** change rollback requirements (DES) for exp_rowsize.
836           */
837           update sysindexes
838           set exp_rowsize = 0
839           where id = @objid and indid = @indid and exp_rowsize is null
840           plan "(update (i_scan csysindexes sysindexes))"
841       end
842       else if @optname = "fillfactor"
843       begin
844           /* 
845           ** Change the fill_factor value of the table/index. 
846           */
847           if (@optvalue < 0 or @optvalue > 100 or @optvalue is null)
848           begin
849               /*
850               ** 18572, "The value for attribute '%1!' must be
851               **         between %2! and %3!."
852               */
853               raiserror 18572, @optname, 0, 100
854               return (1)
855           end
856   
857           select @newlimit16 = convert(smallint, @optvalue)
858   
859           /*
860           ** Do nothing if the new specified and current values are the same.
861           */
862           select @curlimit16 = fill_factor
863           from sysindexes
864           where id = @objid and indid = @indid
865   
866           if (@curlimit16 = @newlimit16)
867               goto proc_end
868       end
869       else if (@optname = "max_rows_per_page")
870       begin
871           /*
872           ** Domain validity checks are done by dbcc chgindcachedvalue().
873           */
874           select @newlimit16 = convert(smallint, @optvalue)
875   
876           /*
877           ** Do nothing if the new specified and current values are the same.
878           */
879           select @curlimit16 = maxrowsperpage
880           from sysindexes
881           where id = @objid and indid = @indid
882   
883           if (@curlimit16 = @newlimit16)
884               goto proc_end
885       end
886       else if (@optname = "ptn_locking")
887       begin
888   
889           /* 
890           ** This option is only for the partitioned table
891           */
892           if ((select count(*) from syspartitions where
893                           id = object_id(@objname)
894                           and (indid = 0 or indid = 1)) = 1)
895           begin
896               /*
897               ** 18085, Object is not partitioned.
898               */
899               raiserror 18085, @optname
900               return (1)
901           end
902   
903           if (@optvalue not in (0, 1))
904           begin
905               /*
906               ** 18985, "The value for attribute '%1!' must be
907               **         either 0 and 1"
908               */
909               raiserror 18985, @optname
910               return (1)
911           end
912   
913           if (@optvalue = 1)
914               select @enable = 1
915           else
916               select @enable = 0
917   
918   
919           /* server defined constant for optimistic index lock */
920           select @ptn_locking = 256 * 4
921   
922           begin transaction chg_ptn_locking
923   
924           /* 
925           ** When one user wants to enable/disable ptn locking, other
926           ** users may have access to the same table. Possible inserts 
927           ** from others need to know if partition locking is enable
928           ** or disabled. To prevent read of inconsistent value by 
929           ** others we will lock the table using an exclusive lock.
930           */
931           select @sqltext = "lock table [" + @objname
932               + "] in exclusive mode wait 5"
933           execute (@sqltext)
934   
935           if (@@error != 0)
936           begin
937               rollback transaction
938   
939               /* 
940               ** 18573, "Failed to update attribute '%1!' for object '%2!'."
941               */
942               raiserror 18573, @optname, @objname
943               return (1)
944           end
945   
946           /* Make sure we do have EX_TAB lock acquired on the table */
947           select @dbname = db_name()
948           select @dbid = db_id()
949   
950           if not exists (select 1 from master..syslocks
951                   where id = object_id(@objname)
952                       and dbid = @dbid
953                       and (type & 1) != 0
954                       and spid = @@spid)
955           begin
956               rollback transaction
957   
958               /*
959               ** 19118, "Object '%1!' is currently being accessed by other
960               ** users. Failed to update attribute '%2!'."
961               */
962               raiserror 19118, @objname, @optname
963               return (1)
964           end
965   
966   
967           if (@enable = 1)
968           begin
969               update sysobjects
970               set sysstat3 = sysstat3 | @ptn_locking
971               where name = @objname
972           end
973           else
974           begin
975               update sysobjects
976               set sysstat3 = sysstat3 & ~ @ptn_locking
977               where name = @objname
978           end
979   
980           if (@@error != 0)
981           begin
982               rollback transaction
983   
984               /*
985               ** 18987:  The attribute specified '%1!' could not 
986               **	   be set for the object. "
987               */
988               raiserror 18987, @optname
989               return (@@error)
990           end
991   
992           /*
993           ** Now, update the in-memory value of the parameter and check
994           ** whether it succeeded.
995           */
996           select @objid = object_id(@objname)
997           dbcc set_ptn_locking(@dbid, @objid, @enable)
998   
999           /* If there was an error, @@error will be non-zero */
1000          if (@@error != 0)
1001          begin
1002              rollback transaction
1003  
1004              /*
1005              ** 18573, "Failed to update attribute '%1!' for object '%2!'."
1006              */
1007              raiserror 18573, @optname, @objname
1008              return (1)
1009          end
1010  
1011          commit transaction
1012  
1013          /*
1014          ** We don't want the exec of sp_chgattribute to
1015          ** be replicated for this option (see comment above
1016          ** where we comment each option).
1017          */
1018          goto proc_end
1019      end
1020  
1021      /* create a temp table for use by identity_burn_max */
1022      create table #maxid(id numeric(38, 0) null)
1023  
1024      /* 
1025      ** Now, do the actual work of updating system catalogs and the in-memory cache.
1026      */
1027  
1028      /*
1029      ** IMPORTANT: The name rs_logexec is significant and is used by 
1030      **            Replication Server
1031      */
1032      begin transaction rs_logexec
1033  
1034      if (@optname not in (
1035                  "exp_row_size",
1036                  "dealloc_first_txtpg",
1037                  "identity_burn_max"))
1038      begin
1039          if (@optname = "reservepagegap")
1040  
1041              update sysindexes
1042              set res_page_gap = @newlimit16
1043              where id = @objid and indid = @indid
1044              plan "(update (i_scan csysindexes sysindexes))"
1045  
1046          else if (@optname = "max_rows_per_page")
1047  
1048              update sysindexes
1049              set maxrowsperpage = @newlimit16
1050              where id = @objid and indid = @indid
1051              plan "(update (i_scan csysindexes sysindexes))"
1052  
1053          else if (@optname = "fillfactor")
1054  
1055              update sysindexes
1056              set fill_factor = @newlimit16
1057              where id = @objid and indid = @indid
1058              plan "(update (i_scan csysindexes sysindexes))"
1059  
1060          else if (@optname = "concurrency_opt_threshold")
1061  
1062              update systabstats
1063              set conopt_thld = @newlimit16
1064              where id = @objid
1065                  and indid = 0
1066  
1067          else if (@optname = "plldegree")
1068  
1069              update systabstats
1070              set plldegree = @newlimit16
1071              where id = @objid
1072                  and indid = @indid
1073  
1074          else if (@optname = "identity_gap")
1075  
1076              update sysindexes
1077              set identitygap = @newlimit32
1078              where id = @objid and indid = @indid
1079              plan "(update (i_scan csysindexes sysindexes))"
1080  
1081          /* else do_nothing */
1082  
1083          /* If there was an error, @@error will be non-zero */
1084          if @@error != 0
1085          begin
1086              rollback transaction
1087  
1088              /*
1089              ** 18573, "Failed to update attribute '%1!' for object '%2!'."
1090              */
1091              raiserror 18573, @optname, @objname
1092              return (1)
1093          end
1094      end
1095  
1096      if (@optname not in
1097              ("dealloc_first_txtpg",
1098                  "identity_burn_max",
1099                  "local_unique_index"))
1100      begin
1101          /*
1102          ** Now, update the in-memory value of the parameter and check
1103          ** whether it succeeded.
1104          */
1105          if (@optname = "identity_gap")
1106              select @dbcc_arg_4 = @newlimit32
1107          else
1108              select @dbcc_arg_4 = convert(int, @newlimit16)
1109  
1110          dbcc chgindcachedvalue(@optname, @tabname, @dbcc_arg_3, @dbcc_arg_4)
1111  
1112          /* If there was an error, @@error will be non-zero */
1113          if @@error != 0
1114          begin
1115              rollback transaction
1116  
1117              /*
1118              ** 18573, "Failed to update attribute '%1!' for object '%2!'."
1119              */
1120              raiserror 18573, @optname, @objname
1121              return (1)
1122          end
1123      end
1124  
1125      if (@optname = "identity_burn_max")
1126      begin
1127          if (@optvalue2 is NULL or @optvalue2 = '')
1128          begin
1129              rollback transaction
1130  
1131              /*
1132              ** 19115, A value must be specified for attribute '%1!'.
1133              */
1134              raiserror 19115, @optname
1135              return (1)
1136          end
1137  
1138          /* get the name of identity colum from syscolumns */
1139          select @colname = name from syscolumns
1140          where id = object_id(@objname)
1141              and (status & 128 != 0)
1142  
1143          /* Table does not have identity column */
1144          if (@colname is NULL)
1145          begin
1146              rollback transaction
1147  
1148              /*
1149              ** 19116, Table '%1!' does not have an identity column.
1150              */
1151              raiserror 19116, @objname
1152              return (1)
1153          end
1154  
1155          /* When one user wants to set the identity value, other users
1156          ** may have access to the same table. Possible inserts from
1157          ** others will affect the max value of the identity column
1158          ** we need for this option. To prevent from this, we will
1159          ** lock the table using an exclusive lock.
1160          */
1161          select @sqltext = "lock table " + @objname
1162              + " in exclusive mode wait 5"
1163          execute (@sqltext)
1164  
1165          if (@@error != 0)
1166          begin
1167              rollback transaction
1168  
1169              /* 
1170              ** 18573, "Failed to update attribute '%1!' for object '%2!'."
1171              */
1172              raiserror 18573, @optname, @objname
1173          end
1174  
1175          /* Make sure we do have EX_TAB lock acquired on the table */
1176          select @dbname = db_name()
1177          select @dbid = db_id()
1178  
1179          if not exists (select 1 from master..syslocks
1180                  where id = object_id(@objname)
1181                      and dbid = @dbid
1182                      and (type & 1) != 0
1183                      and spid = @@spid)
1184          begin
1185              rollback transaction
1186  
1187              /*
1188              ** 19118, "Object '%1!' is currently being accessed by other
1189              ** users. Failed to update attribute '%2!'."
1190              */
1191              raiserror 19118, @objname, @optname
1192              return (1)
1193          end
1194  
1195  
1196          select @new_idtval = convert(numeric(38, 0), @optvalue2)
1197  
1198          /* get the current max identity value in the table */
1199          select @sqltext = "insert #maxid select "
1200              + "max(" + @colname + ") from " + @objname
1201          execute (@sqltext)
1202  
1203          if (@@error != 0)
1204          begin
1205              rollback transaction
1206  
1207              /*
1208              ** 18573, "Failed to update attribute '%1!' for object '%2!'."
1209              */
1210              raiserror 18573, @optname, @objname
1211          end
1212  
1213          /*
1214          ** When a user wants to set a value less than 
1215          ** the current max identity value, it may cause a duplicate
1216          ** with the existing identity value in the table. This is 
1217          ** therefore only allowed when the table has no rows.
1218          */
1219          select @curr_max_idtval = id from #maxid
1220  
1221          if (@curr_max_idtval > 0) and
1222              (@new_idtval < @curr_max_idtval)
1223          begin
1224              rollback transaction
1225  
1226              /*
1227              ** 19117, "The value %1! for '%2!' attribute must be 
1228              ** greater than or equal to the current max identity 
1229              ** value %3!."
1230              */
1231              raiserror 19117, @new_idtval, @optname, @curr_max_idtval
1232              return (1)
1233          end
1234  
1235          dbcc set_identity_burn_max(@dbname, @objname, @optvalue2)
1236  
1237          /* If there was an error, @@error will be non-zero */
1238          if (@@error != 0)
1239          begin
1240              rollback transaction
1241  
1242              /*
1243              ** 18573, "Failed to update attribute '%1!' for object '%2!'."
1244              */
1245              raiserror 18573, @optname, @objname
1246              return (1)
1247          end
1248      end
1249  
1250      /*
1251      ** Write the log record to replicate this invocation 
1252      ** of the stored procedure.
1253      */
1254      if (logexec() != 1)
1255      begin
1256          rollback transaction
1257  
1258          /*
1259          ** 17756, "The execution of the stored procedure '%1!'
1260          ** 	   in database '%2!' was aborted because there
1261          ** 	   was an error in writing the replication log
1262          **	   record."
1263          */
1264          select @dbname = db_name()
1265          raiserror 17756, "sp_chgattribute", @dbname
1266          return (1)
1267      end
1268  
1269      /* If all was successful, now commit the changes */
1270      commit transaction
1271  
1272  
1273  proc_end:
1274      /*
1275      ** We are done. 
1276      ** 18122, "'%1!' attribute of object '%2!' changed to %3!."
1277      */
1278      exec sp_getmessage 18122, @msg output
1279  
1280      if (@optname = "identity_burn_max")
1281          print @msg, @optname, @objname, @optvalue2
1282      else
1283          print @msg, @optname, @objname, @optvalue
1284  
1285      return (0)
1286  


exec sp_procxmode 'sp_chgattribute', 'AnyMode'
go

Grant Execute on sp_chgattribute to public
go
DEFECTS
 MCTR 4 Conditional Begin Tran or Commit Tran 399
 MCTR 4 Conditional Begin Tran or Commit Tran 443
 MCTR 4 Conditional Begin Tran or Commit Tran 482
 MCTR 4 Conditional Begin Tran or Commit Tran 525
 MCTR 4 Conditional Begin Tran or Commit Tran 555
 MCTR 4 Conditional Begin Tran or Commit Tran 612
 MCTR 4 Conditional Begin Tran or Commit Tran 922
 MCTR 4 Conditional Begin Tran or Commit Tran 1011
 MEST 4 Empty String will be replaced by Single Space 1127
 MTYP 4 Assignment type mismatch @tabname: varchar(255) = varchar(767) 162
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
116
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {indid}
155
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {indid}
167
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
338
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 156
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 553
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 563
 QTYP 4 Comparison type mismatch smallint = int 563
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 573
 QTYP 4 Comparison type mismatch smallint = int 573
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 694
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 820
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 894
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 952
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 954
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1065
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1181
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1183
 TNOI 4 Table with no index master..syslocks master..syslocks
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 151
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 153
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 153
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 163
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 166
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 166
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 204
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 340
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 553
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 563
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 573
 MAW1 3 Warning message on %name% sybsystemprocs..systabstats.id: Warning message on systabstats 694
 MAW1 3 Warning message on %name% sybsystemprocs..systabstats.id: Warning message on systabstats 718
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 749
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 777
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 794
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 820
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 839
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 864
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 881
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 893
 MAW1 3 Warning message on %name% master..syslocks.id: Warning message on syslocks 951
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 1043
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 1050
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 1057
 MAW1 3 Warning message on %name% sybsystemprocs..systabstats.id: Warning message on systabstats 1064
 MAW1 3 Warning message on %name% sybsystemprocs..systabstats.id: Warning message on systabstats 1071
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 1078
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 1140
 MAW1 3 Warning message on %name% master..syslocks.id: Warning message on syslocks 1180
 MAW1 3 Warning message on %name% tempdb..#maxid.id: Warning message on #maxid_crby_sybsystemprocs__sp_chgattribute 1219
 MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain 45
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public master..syslocks  
 MGTP 3 Grant to public sybsystemprocs..sp_chgattribute  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..syspartitions  
 MGTP 3 Grant to public sybsystemprocs..systabstats  
 MNER 3 No Error Check should check @@error after update 403
 MNER 3 No Error Check should check @@error after update 409
 MNER 3 No Error Check should check @@error after update 486
 MNER 3 No Error Check should check @@error after update 492
 MNER 3 No Error Check should check @@error after update 561
 MNER 3 No Error Check should check @@error after update 571
 MNER 3 No Error Check should check @@error after update 837
 MNER 3 No Error Check should check @@error after update 969
 MNER 3 No Error Check should check @@error after update 975
 MNER 3 No Error Check should check @@error after update 1041
 MNER 3 No Error Check should check @@error after update 1048
 MNER 3 No Error Check should check @@error after update 1055
 MNER 3 No Error Check should check @@error after update 1062
 MNER 3 No Error Check should check @@error after update 1069
 MNER 3 No Error Check should check @@error after update 1076
 MNER 3 No Error Check should check return value of exec 1278
 MUCO 3 Useless Code Useless Brackets 96
 MUCO 3 Useless Code Useless Brackets 111
 MUCO 3 Useless Code Useless Brackets 123
 MUCO 3 Useless Code Useless Brackets 132
 MUCO 3 Useless Code Useless Brackets 142
 MUCO 3 Useless Code Useless Brackets 175
 MUCO 3 Useless Code Useless Brackets 182
 MUCO 3 Useless Code Useless Brackets 206
 MUCO 3 Useless Code Useless Brackets 211
 MUCO 3 Useless Code Useless Brackets 219
 MUCO 3 Useless Code Useless Brackets 229
 MUCO 3 Useless Code Useless Brackets 235
 MUCO 3 Useless Code Useless Brackets 347
 MUCO 3 Useless Code Useless Brackets 350
 MUCO 3 Useless Code Useless Brackets 356
 MUCO 3 Useless Code Useless Brackets 362
 MUCO 3 Useless Code Useless Brackets 366
 MUCO 3 Useless Code Useless Brackets 376
 MUCO 3 Useless Code Useless Brackets 383
 MUCO 3 Useless Code Useless Brackets 386
 MUCO 3 Useless Code Useless Brackets 393
 MUCO 3 Useless Code Useless Brackets 401
 MUCO 3 Useless Code Useless Brackets 414
 MUCO 3 Useless Code Useless Brackets 424
 MUCO 3 Useless Code Useless Brackets 431
 MUCO 3 Useless Code Useless Brackets 440
 MUCO 3 Useless Code Useless Brackets 452
 MUCO 3 Useless Code Useless Brackets 459
 MUCO 3 Useless Code Useless Brackets 466
 MUCO 3 Useless Code Useless Brackets 469
 MUCO 3 Useless Code Useless Brackets 476
 MUCO 3 Useless Code Useless Brackets 484
 MUCO 3 Useless Code Useless Brackets 497
 MUCO 3 Useless Code Useless Brackets 506
 MUCO 3 Useless Code Useless Brackets 513
 MUCO 3 Useless Code Useless Brackets 522
 MUCO 3 Useless Code Useless Brackets 531
 MUCO 3 Useless Code Useless Brackets 539
 MUCO 3 Useless Code Useless Brackets 546
 MUCO 3 Useless Code Useless Brackets 557
 MUCO 3 Useless Code Useless Brackets 580
 MUCO 3 Useless Code Useless Brackets 589
 MUCO 3 Useless Code Useless Brackets 592
 MUCO 3 Useless Code Useless Brackets 599
 MUCO 3 Useless Code Useless Brackets 609
 MUCO 3 Useless Code Useless Brackets 615
 MUCO 3 Useless Code Useless Brackets 620
 MUCO 3 Useless Code Useless Brackets 627
 MUCO 3 Useless Code Useless Brackets 630
 MUCO 3 Useless Code Useless Brackets 637
 MUCO 3 Useless Code Useless Brackets 642
 MUCO 3 Useless Code Useless Brackets 649
 MUCO 3 Useless Code Useless Brackets 667
 MUCO 3 Useless Code Useless Brackets 674
 MUCO 3 Useless Code Useless Brackets 677
 MUCO 3 Useless Code Useless Brackets 684
 MUCO 3 Useless Code Useless Brackets 696
 MUCO 3 Useless Code Useless Brackets 701
 MUCO 3 Useless Code Useless Brackets 708
 MUCO 3 Useless Code Useless Brackets 720
 MUCO 3 Useless Code Useless Brackets 732
 MUCO 3 Useless Code Useless Brackets 739
 MUCO 3 Useless Code Useless Brackets 751
 MUCO 3 Useless Code Useless Brackets 759
 MUCO 3 Useless Code Useless Brackets 767
 MUCO 3 Useless Code Useless Brackets 779
 MUCO 3 Useless Code Useless Brackets 796
 MUCO 3 Useless Code Useless Brackets 847
 MUCO 3 Useless Code Useless Brackets 854
 MUCO 3 Useless Code Useless Brackets 866
 MUCO 3 Useless Code Useless Brackets 869
 MUCO 3 Useless Code Useless Brackets 883
 MUCO 3 Useless Code Useless Brackets 886
 MUCO 3 Useless Code Useless Brackets 892
 MUCO 3 Useless Code Useless Brackets 900
 MUCO 3 Useless Code Useless Brackets 903
 MUCO 3 Useless Code Useless Brackets 910
 MUCO 3 Useless Code Useless Brackets 913
 MUCO 3 Useless Code Useless Brackets 935
 MUCO 3 Useless Code Useless Brackets 943
 MUCO 3 Useless Code Useless Brackets 963
 MUCO 3 Useless Code Useless Brackets 967
 MUCO 3 Useless Code Useless Brackets 980
 MUCO 3 Useless Code Useless Brackets 989
 MUCO 3 Useless Code Useless Brackets 1000
 MUCO 3 Useless Code Useless Brackets 1008
 MUCO 3 Useless Code Useless Brackets 1034
 MUCO 3 Useless Code Useless Brackets 1039
 MUCO 3 Useless Code Useless Brackets 1046
 MUCO 3 Useless Code Useless Brackets 1053
 MUCO 3 Useless Code Useless Brackets 1060
 MUCO 3 Useless Code Useless Brackets 1067
 MUCO 3 Useless Code Useless Brackets 1074
 MUCO 3 Useless Code Useless Brackets 1092
 MUCO 3 Useless Code Useless Brackets 1096
 MUCO 3 Useless Code Useless Brackets 1105
 MUCO 3 Useless Code Useless Brackets 1121
 MUCO 3 Useless Code Useless Brackets 1125
 MUCO 3 Useless Code Useless Brackets 1127
 MUCO 3 Useless Code Useless Brackets 1135
 MUCO 3 Useless Code Useless Brackets 1144
 MUCO 3 Useless Code Useless Brackets 1152
 MUCO 3 Useless Code Useless Brackets 1165
 MUCO 3 Useless Code Useless Brackets 1192
 MUCO 3 Useless Code Useless Brackets 1203
 MUCO 3 Useless Code Useless Brackets 1232
 MUCO 3 Useless Code Useless Brackets 1238
 MUCO 3 Useless Code Useless Brackets 1246
 MUCO 3 Useless Code Useless Brackets 1254
 MUCO 3 Useless Code Useless Brackets 1266
 MUCO 3 Useless Code Useless Brackets 1280
 MUCO 3 Useless Code Useless Brackets 1285
 QAFM 3 Var Assignment from potentially many rows 337
 QAFM 3 Var Assignment from potentially many rows 692
 QAFM 3 Var Assignment from potentially many rows 716
 QAFM 3 Var Assignment from potentially many rows 1139
 QAFM 3 Var Assignment from potentially many rows 1219
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE plan..."(update (i_scan csysindexes sysindexes))" 561
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE plan..."(update (i_scan csysindexes sysindexes))" 571
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE plan..."(update (i_scan csysindexes sysindexes))" 837
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE plan..."(update (i_scan csysindexes sysindexes))" 1041
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE plan..."(update (i_scan csysindexes sysindexes))" 1048
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE plan..."(update (i_scan csysindexes sysindexes))" 1055
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE plan..."(update (i_scan csysindexes sysindexes))" 1076
 QISO 3 Set isolation level 103
 QNAJ 3 Not using ANSI Inner Join 152
 QNAJ 3 Not using ANSI Inner Join 164
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
405
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
411
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
488
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
494
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: systabstats.csystabstats unique clustered
(id, indid, partitionid)
Intersection: {indid, id}
694
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: systabstats.csystabstats unique clustered
(id, indid, partitionid)
Intersection: {indid, id}
718
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
820
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syspartitions.csyspartitions unique clustered
(id, indid, partitionid)
Intersection: {id, indid}
893
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
971
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
977
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: systabstats.csystabstats unique clustered
(id, indid, partitionid)
Intersection: {indid, id}
1064
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: systabstats.csystabstats unique clustered
(id, indid, partitionid)
Intersection: {indid, id}
1071
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
1140
 MDYS 2 Dynamic SQL Marker 933
 MDYS 2 Dynamic SQL Marker 1163
 MDYS 2 Dynamic SQL Marker 1201
 MSUB 2 Subquery Marker 114
 MSUB 2 Subquery Marker 339
 MSUB 2 Subquery Marker 892
 MSUB 2 Subquery Marker 950
 MSUB 2 Subquery Marker 1179
 MTR1 2 Metrics: Comments Ratio Comments: 52% 45
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 90 = 132dec - 44exi + 2 45
 MTR3 2 Metrics: Query Complexity Complexity: 558 45
 PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, o=sybsystemprocs..sysobjects} 0 151
 PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, o=sybsystemprocs..sysobjects} 0 163

DATA PROPAGATION detailed
ColumnWritten To
@optvaluesysindexes.maxrowsperpage   °.fill_factor   °.res_page_gap   °.identitygap   systabstats.conopt_thld   °.plldegree   sp_estspace_rset_001.Total_Mbytes sp_estspace_rset_002.Total_Mbytes sp_help_rset_001.exp_row_size sp_help_rset_002.optimistic_index_lock
°.dealloc_first_txtpg °.cached_index_root_page

DEPENDENCIES
PROCS AND TABLES USED
read_writes table sybsystemprocs..systabstats  
reads table sybsystemprocs..syscolumns  
reads table master..sysdatabases (1)  
read_writes table sybsystemprocs..sysobjects  
read_writes table tempdb..#maxid (1) 
calls proc sybsystemprocs..sp_optimal_text_space  
   calls proc sybsystemprocs..sp_namecrack  
   read_writes table sybsystemprocs..sysobjects  
   reads table master..sysdatabases (1)  
   calls proc sybsystemprocs..sp_aux_checkroleperm  
      reads table master..syscurconfigs (1)  
      reads table master..sysconfigures (1)  
   reads table sybsystemprocs..sysindexes  
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  
reads table master..syslocks (1)  
reads table sybsystemprocs..syspartitions  
read_writes table sybsystemprocs..sysindexes  
reads table master..spt_values (1)