Database | Proc | Application | Created | Links |
sybsystemprocs | sp_placeobject ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "@(#) generic/sproc/src/%M% %I% %G%" */ 3 /* 5.0 14.2 11/12/91 sproc/src/placeobject */ 4 5 /* 6 ** Messages for "sp_placeobject" 17730 7 ** 8 ** 17460, "Object must be in the current database." 9 ** 17520, "There is no such segment as '%1!'." 10 ** 17730, "Use sp_logdevice to move syslogs table." 11 ** 17731, "You can't move system tables." 12 ** 17732, "You do not own table '%1!'." 13 ** 17733, "There is no table named '%1!'." 14 ** 17734, "There is no index named '%1!' for table '%2!'." 15 ** 17735, "'%1!' is now on segment '%2!'." 16 ** 17736, "You can't place a user table/index to logsegment." 17 ** 18336, "Permission denied. Your curwrite label must be 18 ** set at the hurdle of the affected database." 19 ** 17738, "sp_placeobject is not allowed for %1!, as it is 20 ** a virtually hashed table." 21 ** 17739, "Segment %1! has a virtually hashed table; therefore, 22 ** you cannot place an object on this segment." 23 */ 24 /* 25 ** NOTE: 26 ** All updates to sysindexes are done by forcing the index access via 27 ** 'csysindexes' so that it's clear that only one row is being updated. 28 ** (Multi-row updates to sysindexes are prohibited.) 29 */ 30 create procedure sp_placeobject 31 @segname varchar(255), /* segment name */ 32 @objname varchar(1023) /* object name */ 33 as 34 35 declare @msg varchar(1024) /* message text */ 36 declare @tabname varchar(767) /* table name, may be qualified */ 37 declare @indexname varchar(255) /* index name, unqualified */ 38 declare @indexid smallint /* index ID */ 39 declare @segment smallint /* segment number */ 40 declare @procval int /* 1 if user has sa_role, 0 otherwise */ 41 declare @revobjname varchar(1023) /* reverse of @objname */ 42 declare @hash_bit int /* used for virtually hashed table */ 43 44 45 46 if @@trancount = 0 47 begin 48 set chained off 49 end 50 51 set transaction isolation level 1 52 53 /* 54 ** Check to see if the segment exists. 55 */ 56 select @segment = segment 57 from syssegments 58 where name = @segname 59 if @segment is null 60 begin 61 /* 62 ** 17520, "There is no such segment as '%1!'." 63 */ 64 raiserror 17520, @segname 65 return (1) 66 end 67 68 /* 69 ** Are we dealing with a table or its index? 70 */ 71 if (@objname like "%.%") and (object_id(@objname) is null) 72 begin 73 /* @objname is in the form [database.][owner.]table.index */ 74 select @revobjname = reverse(@objname) 75 select 76 @tabname = reverse(substring(@revobjname, 77 charindex(".", @revobjname) + 1, 767)), 78 @indexname = reverse(substring(@revobjname, 1, 79 charindex(".", @revobjname) - 1)) 80 end 81 else 82 begin 83 /* @objname is in the form [database.][owner.]table */ 84 select @tabname = @objname, @indexname = null 85 end 86 87 /* 88 ** Make sure the table is local to the current database. 89 */ 90 if @tabname like "%.%.%" and 91 substring(@tabname, 1, charindex(".", @tabname) - 1) != db_name() 92 begin 93 /* 17460, "Object must be in the current database." */ 94 raiserror 17460 95 return (1) 96 end 97 98 /* 99 ** make sure the table exists. 100 */ 101 if (object_id(@tabname) is null) 102 begin 103 /* 104 ** 17733, "There is no table named '%1!'." 105 */ 106 raiserror 17733, @tabname 107 return (1) 108 end 109 110 /* 111 ** Can't use placeobject to move syslogs. 112 */ 113 if (object_id(@tabname) = 8) 114 begin 115 /* 116 ** 17730, "Use sp_logdevice to move syslogs table." 117 */ 118 raiserror 17730 119 return (1) 120 end 121 122 /* 123 ** Can't move system tables. 124 */ 125 if (object_id(@tabname) < 100) 126 begin 127 /* 128 ** 17731, "You can't move system tables." 129 */ 130 raiserror 17731 131 return (1) 132 end 133 134 /* 135 ** Only the Database Owner (DBO) or Table Owner (TBO) or 136 ** Accounts with SA role can execute it. 137 ** Call proc_role() with the required SA role. 138 */ 139 if user_id() != 1 140 begin 141 if not exists (select * from sysobjects 142 where id = object_id(@tabname) and uid = user_id()) 143 begin 144 if (proc_role("sa_role") < 1) 145 begin 146 /* 147 ** 17732, "You do not own table '%1!'." 148 */ 149 raiserror 17732, @tabname 150 return (1) 151 end 152 end 153 end 154 155 select @hash_bit = number from master.dbo.spt_values 156 where name = "virtually hashed table" and type = "O2" 157 if exists (select * from sysobjects 158 where id = object_id(@tabname) and @hash_bit = sysstat2 & @hash_bit) 159 begin 160 /* 161 ** 17738, "sp_placeobject is not allowed for %1!, as it is 162 ** a virtually hashed table." 163 */ 164 raiserror 17738, @tabname 165 return (1) 166 end 167 168 /* If user has sa role audit this as a successful sa 169 ** command execution. 170 */ 171 if charindex("sa_role", show_role()) > 0 172 select @procval = proc_role("sa_role") 173 174 /* 175 ** If we are dealing with an index, make sure it exists. 176 ** Note: Its important here to use the 'name' column of sysindexes 177 ** with the convert() to allow it to work for the names with trailing 178 ** spaces as we truncate trailing spaces in varchar columns except 179 ** for some system tables. 180 */ 181 if @indexname is not null and 182 not exists (select * from sysindexes 183 where id = object_id(@tabname) and 184 convert(varchar(255), name) = @indexname) 185 begin 186 /* 187 ** 17734, "There is no index named '%1!' for table '%2!'." 188 */ 189 raiserror 17734, @indexname, @tabname 190 return (1) 191 end 192 193 194 if (@segname = "logsegment") 195 begin 196 /* 17736, "You can't place a user table/index to logsegment." */ 197 raiserror 17736 198 return (1) 199 end 200 201 if exists (select * from syssegments 202 where name = @segname and 2 = status & 2) 203 begin 204 /* 205 ** 17739, "An object cannot be placed on segment %1!. As this segment has a virtually hashed table." 206 */ 207 raiserror 17739, @segname 208 return (1) 209 end 210 211 212 /* 213 ** Everything's ok so change the segment for the object. 214 */ 215 declare @dbname varchar(255) 216 declare @objid int 217 declare @ptnid int 218 declare @placementptnid int 219 declare @placementidx smallint 220 declare @moreupdates smallint 221 222 select @dbname = db_name() 223 select @objid = object_id(@tabname) 224 225 /* 226 ** If the table has a placement index (bit 0x200 = 512) we need 227 ** to consider the below two cases: 228 ** 229 ** 1) if @indexname is null then we need to update syspartitions 230 ** for indid 0 as well for the placement index. 231 ** 232 ** 2) if @indexname is not null then we need to update syspartitions 233 ** for this index as well for indid 0 if this index is the 234 ** placement index of the table. 235 ** 236 ** We can't disassociate a placement index from its data pages 237 ** likewise we do for an APL table and its clustered index, 238 ** where in that case we instead have a single row in sysindexes 239 ** with indid 1. 240 */ 241 select @placementidx = 0 242 select @placementidx = indid 243 from sysindexes 244 where id = @objid and indid > 1 and status2 & 512 = 512 245 246 /* Assume we don't have to do more than one update of sysindexes. */ 247 select @moreupdates = 0 248 249 if @indexname is null 250 begin 251 select @indexid = indid 252 from sysindexes 253 where id = @objid and indid < 2 254 255 if (@placementidx > 1) 256 select @moreupdates = 1 257 end 258 else 259 begin 260 /* 261 ** Note: Its important here to use the 'name' column of sysindexes 262 ** with the convert() to allow it to work for the names with trailing 263 ** spaces as we truncate trailing spaces in varchar columns except 264 ** for some system tables. 265 */ 266 select @indexid = indid 267 from sysindexes 268 where id = @objid and convert(varchar(255), name) = @indexname 269 270 if (@placementidx > 1 and @indexid = @placementidx) 271 begin 272 select @moreupdates = 1 273 274 /* 275 ** Force it to be 0 so that we update below 276 ** sysindexes row for indid 0. 277 */ 278 select @placementidx = 0 279 end 280 end 281 282 283 begin tran updatesys 284 285 /* Get the partition ids of affected index*/ 286 declare ptnid_crsr cursor 287 for select partitionid from syspartitions 288 where id = @objid and indid = @indexid 289 for read only 290 291 update sysindexes 292 set segment = @segment 293 from sysindexes 294 where id = @objid and indid = @indexid 295 plan "(update (i_scan csysindexes sysindexes))" 296 297 if (@@error != 0) 298 begin 299 rollback tran updatesys 300 return (1) 301 end 302 303 /* 304 ** We updated a sysindexes row. We also need to update the in-core 305 ** structure representing this sysindexes row as the sysindexes rows 306 ** cache is not a write thru cache. 307 */ 308 dbcc refreshides(@dbname, @objid, @indexid, "placeobject") 309 310 if (@@error != 0) 311 begin 312 rollback tran updatesys 313 return (1) 314 end 315 316 update syspartitions 317 set segment = @segment 318 from syspartitions 319 where id = @objid and indid = @indexid 320 321 if (@@error != 0) 322 begin 323 rollback tran updatesys 324 return (1) 325 end 326 327 /* 328 ** We updated syspartitions row. We also need to update the in-core 329 ** structure representing this syspartitions row. 330 */ 331 open ptnid_crsr 332 fetch ptnid_crsr into @ptnid 333 while (@@sqlstatus = 0) 334 begin 335 dbcc refreshpdes(@dbname, @objid, @indexid, @ptnid, "placeobject") 336 337 if (@@error != 0) 338 begin 339 rollback tran updatesys 340 return (1) 341 end 342 fetch ptnid_crsr into @ptnid 343 end 344 close ptnid_crsr 345 deallocate cursor ptnid_crsr 346 347 if (@moreupdates = 1) 348 begin 349 declare placementptnid_crsr cursor 350 for select partitionid from syspartitions 351 where id = @objid and indid = @placementidx 352 for read only 353 354 update sysindexes 355 set segment = @segment 356 from sysindexes 357 where id = @objid and indid = @placementidx 358 plan "(update (i_scan csysindexes sysindexes))" 359 360 if (@@error != 0) 361 begin 362 rollback tran updatesys 363 return (1) 364 end 365 366 /* 367 ** We updated a sysindexes row. We also need to update the in-core 368 ** structure representing this sysindexes row as the sysindexes rows 369 ** cache is not a write thru cache. 370 */ 371 dbcc refreshides(@dbname, @objid, @placementidx, "placeobject") 372 373 if (@@error != 0) 374 begin 375 rollback tran updatesys 376 return (1) 377 end 378 379 update syspartitions 380 set segment = @segment 381 from syspartitions 382 where id = @objid 383 and indid = @placementidx 384 385 if (@@error != 0) 386 begin 387 rollback tran updatesys 388 return (1) 389 end 390 391 /* 392 ** We updated syspartitions row. We also need to update the in-core 393 ** structure representing this syspartitions row. 394 */ 395 open placementptnid_crsr 396 fetch placementptnid_crsr into @placementptnid 397 while (@@sqlstatus = 0) 398 begin 399 dbcc refreshpdes(@dbname, @objid, @placementidx, 400 @placementptnid, "placeobject") 401 402 if (@@error != 0) 403 begin 404 rollback tran updatesys 405 return (1) 406 end 407 fetch placementptnid_crsr into @placementptnid 408 end 409 close placementptnid_crsr 410 deallocate cursor placementptnid_crsr 411 end 412 413 commit tran updatesys 414 415 dbcc dbrepair(@dbname, remap, NULL, - 1, @segname) 416 /* 417 ** 17735, "'%1!' is now on segment '%2!'." 418 */ 419 exec sp_getmessage 17735, @msg output 420 print @msg, @objname, @segname 421 422 return (0) 423
exec sp_procxmode 'sp_placeobject', 'AnyMode' go Grant Execute on sp_placeobject to public go
DEPENDENCIES |
PROCS AND TABLES USED read_writes table sybsystemprocs..sysindexes ![]() reads table sybsystemprocs..sysobjects ![]() reads table sybsystemprocs..syssegments ![]() read_writes table sybsystemprocs..syspartitions ![]() reads table master..spt_values (1) ![]() calls proc sybsystemprocs..sp_getmessage ![]() reads table sybsystemprocs..sysusermessages ![]() reads table master..sysmessages (1) ![]() reads table master..syslanguages (1) ![]() calls proc sybsystemprocs..sp_validlang ![]() reads table master..syslanguages (1) ![]() |