Database | Proc | Application | Created | Links |
sybsystemprocs | sp_chgattribute ![]() | ![]() | 31 Aug 14 | Defects 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 | |
![]() | 392 |
![]() | 436 |
![]() | 470 |
![]() | 527 |
![]() | 908 |
![]() | 158 |
![]() (number, type) Intersection: {type} | 113 |
![]() (id, indid) Intersection: {indid} | 151 |
![]() (id, indid) Intersection: {indid} | 163 |
![]() (number, type) Intersection: {type} | 331 |
![]() | 152 |
![]() | 468 |
![]() | 478 |
![]() | 478 |
![]() | 488 |
![]() | 488 |
![]() | 609 |
![]() | 735 |
![]() | 846 |
![]() | 962 |
![]() | 964 |
![]() | master..syslocks |
![]() | master..spt_values |
![]() | 45 |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | 396 |
![]() | 402 |
![]() | 476 |
![]() | 486 |
![]() | 752 |
![]() | 822 |
![]() | 829 |
![]() | 836 |
![]() | 843 |
![]() | 850 |
![]() | 857 |
![]() | 1059 |
![]() | 93 |
![]() | 108 |
![]() | 120 |
![]() | 129 |
![]() | 138 |
![]() | 171 |
![]() | 178 |
![]() | 202 |
![]() | 207 |
![]() | 215 |
![]() | 225 |
![]() | 231 |
![]() | 340 |
![]() | 343 |
![]() | 349 |
![]() | 355 |
![]() | 359 |
![]() | 369 |
![]() | 376 |
![]() | 379 |
![]() | 386 |
![]() | 394 |
![]() | 407 |
![]() | 417 |
![]() | 424 |
![]() | 433 |
![]() | 446 |
![]() | 454 |
![]() | 461 |
![]() | 472 |
![]() | 495 |
![]() | 504 |
![]() | 507 |
![]() | 514 |
![]() | 524 |
![]() | 530 |
![]() | 535 |
![]() | 542 |
![]() | 545 |
![]() | 552 |
![]() | 557 |
![]() | 564 |
![]() | 582 |
![]() | 589 |
![]() | 592 |
![]() | 599 |
![]() | 611 |
![]() | 616 |
![]() | 623 |
![]() | 635 |
![]() | 647 |
![]() | 654 |
![]() | 666 |
![]() | 674 |
![]() | 682 |
![]() | 694 |
![]() | 711 |
![]() | 762 |
![]() | 769 |
![]() | 781 |
![]() | 784 |
![]() | 798 |
![]() | 815 |
![]() | 820 |
![]() | 827 |
![]() | 834 |
![]() | 841 |
![]() | 848 |
![]() | 855 |
![]() | 873 |
![]() | 877 |
![]() | 886 |
![]() | 902 |
![]() | 906 |
![]() | 908 |
![]() | 916 |
![]() | 925 |
![]() | 933 |
![]() | 946 |
![]() | 973 |
![]() | 984 |
![]() | 1013 |
![]() | 1019 |
![]() | 1027 |
![]() | 1035 |
![]() | 1047 |
![]() | 1061 |
![]() | 1066 |
![]() | 330 |
![]() | 607 |
![]() | 631 |
![]() | 920 |
![]() | 1000 |
![]() | 476 |
![]() | 486 |
![]() | 752 |
![]() | 822 |
![]() | 829 |
![]() | 836 |
![]() | 857 |
![]() | 100 |
![]() | 148 |
![]() | 160 |
![]() (name, uid) Intersection: {name} | 398 |
![]() (name, uid) Intersection: {name} | 404 |
![]() (id, indid, partitionid) Intersection: {indid, id} | 609 |
![]() (id, indid, partitionid) Intersection: {indid, id} | 633 |
![]() (id, number, colid) Intersection: {id} | 735 |
![]() (id, indid, partitionid) Intersection: {indid, id} | 845 |
![]() (id, indid, partitionid) Intersection: {indid, id} | 852 |
![]() (id, number, colid) Intersection: {id} | 921 |
![]() | 944 |
![]() | 982 |
![]() | 111 |
![]() | 332 |
![]() | 960 |
![]() | 45 |
![]() | 45 |
![]() | 45 |
![]() | 147 |
![]() | 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) ![]() |