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


exec sp_procxmode 'sp_chgattribute', 'AnyMode'
go

Grant Execute on sp_chgattribute to public
go
DEFECTS
 MCTR 4 Conditional Begin Tran or Commit Tran 392
 MCTR 4 Conditional Begin Tran or Commit Tran 436
 MCTR 4 Conditional Begin Tran or Commit Tran 470
 MCTR 4 Conditional Begin Tran or Commit Tran 527
 MEST 4 Empty String will be replaced by Single Space 908
 MTYP 4 Assignment type mismatch @tabname: varchar(255) = varchar(767) 158
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
113
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {indid}
151
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {indid}
163
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
331
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 152
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 468
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 478
 QTYP 4 Comparison type mismatch smallint = int 478
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 488
 QTYP 4 Comparison type mismatch smallint = int 488
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 609
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 735
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 846
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 962
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 964
 TNOI 4 Table with no index master..syslocks master..syslocks
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 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..systabstats  
 MNER 3 No Error Check should check @@error after update 396
 MNER 3 No Error Check should check @@error after update 402
 MNER 3 No Error Check should check @@error after update 476
 MNER 3 No Error Check should check @@error after update 486
 MNER 3 No Error Check should check @@error after update 752
 MNER 3 No Error Check should check @@error after update 822
 MNER 3 No Error Check should check @@error after update 829
 MNER 3 No Error Check should check @@error after update 836
 MNER 3 No Error Check should check @@error after update 843
 MNER 3 No Error Check should check @@error after update 850
 MNER 3 No Error Check should check @@error after update 857
 MNER 3 No Error Check should check return value of exec 1059
 MUCO 3 Useless Code Useless Brackets 93
 MUCO 3 Useless Code Useless Brackets 108
 MUCO 3 Useless Code Useless Brackets 120
 MUCO 3 Useless Code Useless Brackets 129
 MUCO 3 Useless Code Useless Brackets 138
 MUCO 3 Useless Code Useless Brackets 171
 MUCO 3 Useless Code Useless Brackets 178
 MUCO 3 Useless Code Useless Brackets 202
 MUCO 3 Useless Code Useless Brackets 207
 MUCO 3 Useless Code Useless Brackets 215
 MUCO 3 Useless Code Useless Brackets 225
 MUCO 3 Useless Code Useless Brackets 231
 MUCO 3 Useless Code Useless Brackets 340
 MUCO 3 Useless Code Useless Brackets 343
 MUCO 3 Useless Code Useless Brackets 349
 MUCO 3 Useless Code Useless Brackets 355
 MUCO 3 Useless Code Useless Brackets 359
 MUCO 3 Useless Code Useless Brackets 369
 MUCO 3 Useless Code Useless Brackets 376
 MUCO 3 Useless Code Useless Brackets 379
 MUCO 3 Useless Code Useless Brackets 386
 MUCO 3 Useless Code Useless Brackets 394
 MUCO 3 Useless Code Useless Brackets 407
 MUCO 3 Useless Code Useless Brackets 417
 MUCO 3 Useless Code Useless Brackets 424
 MUCO 3 Useless Code Useless Brackets 433
 MUCO 3 Useless Code Useless Brackets 446
 MUCO 3 Useless Code Useless Brackets 454
 MUCO 3 Useless Code Useless Brackets 461
 MUCO 3 Useless Code Useless Brackets 472
 MUCO 3 Useless Code Useless Brackets 495
 MUCO 3 Useless Code Useless Brackets 504
 MUCO 3 Useless Code Useless Brackets 507
 MUCO 3 Useless Code Useless Brackets 514
 MUCO 3 Useless Code Useless Brackets 524
 MUCO 3 Useless Code Useless Brackets 530
 MUCO 3 Useless Code Useless Brackets 535
 MUCO 3 Useless Code Useless Brackets 542
 MUCO 3 Useless Code Useless Brackets 545
 MUCO 3 Useless Code Useless Brackets 552
 MUCO 3 Useless Code Useless Brackets 557
 MUCO 3 Useless Code Useless Brackets 564
 MUCO 3 Useless Code Useless Brackets 582
 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 611
 MUCO 3 Useless Code Useless Brackets 616
 MUCO 3 Useless Code Useless Brackets 623
 MUCO 3 Useless Code Useless Brackets 635
 MUCO 3 Useless Code Useless Brackets 647
 MUCO 3 Useless Code Useless Brackets 654
 MUCO 3 Useless Code Useless Brackets 666
 MUCO 3 Useless Code Useless Brackets 674
 MUCO 3 Useless Code Useless Brackets 682
 MUCO 3 Useless Code Useless Brackets 694
 MUCO 3 Useless Code Useless Brackets 711
 MUCO 3 Useless Code Useless Brackets 762
 MUCO 3 Useless Code Useless Brackets 769
 MUCO 3 Useless Code Useless Brackets 781
 MUCO 3 Useless Code Useless Brackets 784
 MUCO 3 Useless Code Useless Brackets 798
 MUCO 3 Useless Code Useless Brackets 815
 MUCO 3 Useless Code Useless Brackets 820
 MUCO 3 Useless Code Useless Brackets 827
 MUCO 3 Useless Code Useless Brackets 834
 MUCO 3 Useless Code Useless Brackets 841
 MUCO 3 Useless Code Useless Brackets 848
 MUCO 3 Useless Code Useless Brackets 855
 MUCO 3 Useless Code Useless Brackets 873
 MUCO 3 Useless Code Useless Brackets 877
 MUCO 3 Useless Code Useless Brackets 886
 MUCO 3 Useless Code Useless Brackets 902
 MUCO 3 Useless Code Useless Brackets 906
 MUCO 3 Useless Code Useless Brackets 908
 MUCO 3 Useless Code Useless Brackets 916
 MUCO 3 Useless Code Useless Brackets 925
 MUCO 3 Useless Code Useless Brackets 933
 MUCO 3 Useless Code Useless Brackets 946
 MUCO 3 Useless Code Useless Brackets 973
 MUCO 3 Useless Code Useless Brackets 984
 MUCO 3 Useless Code Useless Brackets 1013
 MUCO 3 Useless Code Useless Brackets 1019
 MUCO 3 Useless Code Useless Brackets 1027
 MUCO 3 Useless Code Useless Brackets 1035
 MUCO 3 Useless Code Useless Brackets 1047
 MUCO 3 Useless Code Useless Brackets 1061
 MUCO 3 Useless Code Useless Brackets 1066
 QAFM 3 Var Assignment from potentially many rows 330
 QAFM 3 Var Assignment from potentially many rows 607
 QAFM 3 Var Assignment from potentially many rows 631
 QAFM 3 Var Assignment from potentially many rows 920
 QAFM 3 Var Assignment from potentially many rows 1000
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE plan..."(update (i_scan csysindexes sysindexes))" 476
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE plan..."(update (i_scan csysindexes sysindexes))" 486
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE plan..."(update (i_scan csysindexes sysindexes))" 752
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE plan..."(update (i_scan csysindexes sysindexes))" 822
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE plan..."(update (i_scan csysindexes sysindexes))" 829
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE plan..."(update (i_scan csysindexes sysindexes))" 836
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE plan..."(update (i_scan csysindexes sysindexes))" 857
 QISO 3 Set isolation level 100
 QNAJ 3 Not using ANSI Inner Join 148
 QNAJ 3 Not using ANSI Inner Join 160
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
398
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
404
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: systabstats.csystabstats unique clustered
(id, indid, partitionid)
Intersection: {indid, id}
609
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: systabstats.csystabstats unique clustered
(id, indid, partitionid)
Intersection: {indid, id}
633
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
735
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: systabstats.csystabstats unique clustered
(id, indid, partitionid)
Intersection: {indid, id}
845
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: systabstats.csystabstats unique clustered
(id, indid, partitionid)
Intersection: {indid, id}
852
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
921
 MDYS 2 Dynamic SQL Marker 944
 MDYS 2 Dynamic SQL Marker 982
 MSUB 2 Subquery Marker 111
 MSUB 2 Subquery Marker 332
 MSUB 2 Subquery Marker 960
 MTR1 2 Metrics: Comments Ratio Comments: 54% 45
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 78 = 110dec - 34exi + 2 45
 MTR3 2 Metrics: Query Complexity Complexity: 455 45
 PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, o=sybsystemprocs..sysobjects} 0 147
 PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, o=sybsystemprocs..sysobjects} 0 159

DEPENDENCIES
PROCS AND TABLES USED
read_writes table sybsystemprocs..systabstats  
reads table master..syslocks (1)  
reads table master..sysdatabases (1)  
read_writes table tempdb..#maxid (1) 
calls proc sybsystemprocs..sp_optimal_text_space  
   calls proc sybsystemprocs..sp_namecrack  
   reads table sybsystemprocs..sysindexes  
   read_writes table sybsystemprocs..sysobjects  
   reads table master..sysdatabases (1)  
   calls proc sybsystemprocs..sp_is_valid_user  
      read_writes table tempdb..#t (1) 
      reads table master..sysloginroles (1)  
      reads table master..sysdatabases (1)  
read_writes table sybsystemprocs..sysindexes  
read_writes table sybsystemprocs..sysobjects  
calls proc sybsystemprocs..sp_getmessage  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  
   reads table master..syslanguages (1)  
reads table sybsystemprocs..syscolumns  
reads table master..spt_values (1)