DatabaseProcApplicationCreatedLinks
sybsystemprocssp_placeobject  14 déc. 14Defects Propagation Dependencies

1     
2     create or replace procedure sp_placeobject
3         @segname varchar(255), /* segment name */
4         @objname varchar(1023), /* object name */
5         @ptnname varchar(255) = null /* partition name */
6     as
7     
8         declare @msg varchar(1024) /* message text */
9             , @tabname varchar(767) /* table name, may be qualified */
10            , @indexname varchar(255) /* index name, unqualified */
11            , @objid int /* Object ID */
12            , @indexid smallint /* index ID */
13            , @segment smallint /* segment number */
14            , @procval int /* 1 if user has sa_role, 0 otherwise */
15            , @revobjname varchar(1023) /* reverse of @objname */
16            , @hash_bit int /* used for virtually hashed table */
17            , @nullarg varchar(1)
18            , @dummy int
19            , @status int
20            , @status1 int
21            , @gp_enabled int
22            , @dbname varchar(255)
23    
24        select @status1 = 1
25    
26        if @@trancount = 0
27        begin
28            set chained off
29        end
30    
31        set transaction isolation level 1
32    
33        /*
34        **  Check to see if the segment exists.
35        */
36        select @segment = segment
37        from syssegments
38        where name = @segname
39        if @segment is null
40        begin
41            /*
42            ** 17520, "There is no such segment as '%1!'."
43            */
44            raiserror 17520, @segname
45            return (1)
46        end
47    
48        /*
49        **  Are we dealing with a table or its index?
50        */
51        if (@objname like "%.%") and (object_id(@objname) is null)
52        begin
53            /* @objname is in the form [database.][owner.]table.index */
54            select @revobjname = reverse(@objname)
55            select
56                @tabname = reverse(substring(@revobjname,
57                        charindex(".", @revobjname) + 1, 767)),
58                @indexname = reverse(substring(@revobjname, 1,
59                        charindex(".", @revobjname) - 1))
60        end
61        else
62        begin
63            /* @objname is in the form [database.][owner.]table */
64            select @tabname = @objname, @indexname = null
65        end
66    
67        select @dbname = db_name()
68    
69        /*
70        **  Make sure the table is local to the current database.
71        */
72        if @tabname like "%.%.%" and
73            substring(@tabname, 1, charindex(".", @tabname) - 1) != @dbname
74        begin
75            /* 17460, "Object must be in the current database." */
76            raiserror 17460
77            return (1)
78        end
79    
80        select @objid = object_id(@tabname)
81    
82        /*
83        **  make sure the table exists.
84        */
85        if (@objid is null)
86        begin
87            /*
88            ** 17733, "There is no table named '%1!'."
89            */
90            raiserror 17733, @tabname
91            return (1)
92        end
93    
94        /*
95        **  Can't use placeobject to move syslogs.
96        */
97        if (@objid = 8)
98        begin
99            /*
100           ** 17730, "Use sp_logdevice to move syslogs table."
101           */
102           raiserror 17730
103           return (1)
104       end
105   
106       /*
107       **  Can't move system tables.
108       */
109       if (@objid < 256)
110       begin
111           /*
112           ** 17731, "You can't move system tables."
113           */
114           raiserror 17731
115           return (1)
116       end
117   
118       /*
119       **  If granular permissions is not enabled, only the Database Owner (DBO) or 
120       **  Table Owner (TBO) or Accounts with SA role can execute it.
121       **  If granular permissions is enabled, only users with 'manage database' or
122       **  Table Owner (TBO) can execute it. Both proc_role and proc_auditperm will 
123       **  do auditing if required. Both will also print error message if required.
124       */
125   
126       select @dbname = db_name()
127       select @nullarg = NULL
128       execute @status = sp_aux_checkroleperm "dbo", "manage database",
129           @dbname, @gp_enabled output
130   
131       if (@status != 0)
132       begin
133   
134           if not exists (select * from sysobjects
135                   where id = @objid and uid = user_id())
136           begin
137               if (@gp_enabled = 0)
138               begin
139                   execute @status1 = sp_aux_checkroleperm "sa_role",
140                       @nullarg, @nullarg, @gp_enabled output
141                   if (@status1 != 0)
142                   begin
143                       select @dummy = proc_role("sa_role")
144                       /*
145                       ** 17732, "You do not own table '%1!'."
146                       */
147                       raiserror 17732, @tabname
148                       return (1)
149                   end
150               end
151               else
152               begin
153                   select @dummy = proc_auditperm("manage database",
154                           @status, @dbname)
155                   return (1)
156               end
157           end
158       end
159   
160       /* For Auditing */
161       if (@gp_enabled = 0)
162       begin
163           if (@status1 = 0)
164               select @dummy = proc_role("sa_role")
165       end
166       else
167       begin
168           if (@status = 0)
169               select @dummy = proc_auditperm("manage database", @status,
170                       @dbname)
171       end
172   
173       select @hash_bit = number from master.dbo.spt_values
174       where name = "virtually hashed table" and type = "O2"
175       if exists (select * from sysobjects
176               where id = object_id(@tabname) and @hash_bit = sysstat2 & @hash_bit)
177       begin
178           /*
179           ** 17738, "sp_placeobject is not allowed for %1!, as it is
180           **         a virtually hashed table."
181           */
182           raiserror 17738, @tabname
183           return (1)
184       end
185   
186       /* If user has sa role audit this as a successful sa 
187       ** command execution.
188       */
189       if charindex("sa_role", show_role()) > 0
190           select @procval = proc_role("sa_role")
191   
192       /*
193       **  If we are dealing with an index, make sure it exists.
194       **  Note: Its important here to use the 'name' column of sysindexes
195       **  with the convert() to allow it to work for the names with trailing
196       **  spaces as we truncate trailing spaces in varchar columns except
197       **  for some system tables.
198       */
199       if @indexname is not null and
200           not exists (select * from sysindexes
201               where id = @objid
202                   and convert(varchar(255), name) = @indexname)
203       begin
204           /*
205           ** 17734, "There is no index named '%1!' for table '%2!'."
206           */
207           raiserror 17734, @indexname, @tabname
208           return (1)
209       end
210   
211   
212       if (@segname = "logsegment")
213       begin
214           /* 17736, "You can't place a user table/index to logsegment." */
215           raiserror 17736
216           return (1)
217       end
218   
219       if exists (select * from syssegments
220               where name = @segname and 2 = status & 2)
221       begin
222           /*
223           ** 17739, "An object cannot be placed on segment %1!. As this segment has a virtually hashed table."
224           */
225           raiserror 17739, @segname
226           return (1)
227       end
228   
229       /*
230       **  Everything's ok so change the segment for the object.
231       */
232       declare @ptnid int
233           , @placementptnid int
234           , @placementidx smallint
235           , @moreupdates smallint
236           , @error int
237           , @rowcount int
238   
239       /*
240       ** If the table has a placement index (bit 0x200 = 512) we need
241       ** to consider the below two cases:
242       **
243       ** 1)	if @indexname is null then we need to update syspartitions
244       **	for indid 0 as well for the placement index.
245       **
246       ** 2)	if @indexname is not null then we need to update syspartitions
247       **	for this index as well for indid 0 if this index is the
248       **	placement index of the table.
249       **
250       ** We can't disassociate a placement index from its data pages
251       ** likewise we do for an APL table and its clustered index,
252       ** where in that case we instead have a single row in sysindexes
253       ** with indid 1.
254       */
255   
256       select @placementidx = indid
257       from sysindexes
258       where id = @objid and indid > 1 and status2 & 512 = 512
259   
260       if (@placementidx is null)
261           select @placementidx = 0
262   
263       /* Assume we don't have to do more than one update of sysindexes. */
264       select @moreupdates = 0
265   
266       if @indexname is null
267       begin
268           select @indexid = indid
269           from sysindexes
270           where id = @objid and indid < 2
271   
272           if (@placementidx > 1)
273               select @moreupdates = 1
274       end
275       else
276       begin
277           /*
278           **  Note: Its important here to use the 'name' column of sysindexes
279           **  with the convert() to allow it to work for the names with trailing
280           **  spaces as we truncate trailing spaces in varchar columns except
281           **  for some system tables.
282           */
283           select @indexid = indid
284           from sysindexes
285           where id = @objid and convert(varchar(255), name) = @indexname
286   
287           if (@placementidx > 1 and @indexid = @placementidx)
288           begin
289               select @moreupdates = 1
290   
291               /*
292               ** Force it to be 0 so that we update below
293               ** sysindexes row for indid 0.
294               */
295               select @placementidx = 0
296           end
297       end
298   
299       begin tran updatesys
300   
301       if @ptnname is not null
302       begin
303           /*
304           ** If it is APL table partition and we have clustered index, we need 
305           ** to search the @ptnname in 'cdataptnname'. For all other cases, we 
306           ** will search @ptnname in 'name'.
307           */
308           if ((@indexname is null) and (@indexid = 1))
309           begin
310               select @ptnid = partitionid
311               from syspartitions
312               where id = @objid
313                   and indid = @indexid
314                   and cdataptnname = @ptnname
315   
316               /* If it is valid partition, update syspartitions */
317               if @ptnid is not null
318               begin
319                   update syspartitions
320                   set segment = @segment
321                   from syspartitions
322                   where id = @objid
323                       and indid = @indexid
324                       and cdataptnname = @ptnname
325   
326                   set @error = @@error
327                       , @rowcount = @@rowcount
328   
329                   /* We expect only one row should be affected. */
330                   if (@rowcount != 1)
331                   begin
332                       /*
333                       ** 19379, "Update of the table %1! affected
334                       ** %2! rows but expected %3! rows to be
335                       ** Updated. Command aborted.
336                       */
337                       raiserror 19379, @tabname, @rowcount, 1
338                       rollback tran updatesys
339                       return (1)
340                   end
341   
342                   if (@error != 0)
343                   begin
344                       rollback tran updatesys
345                       return (1)
346                   end
347                   goto refresh_pdes
348               end
349           end
350           else
351           begin
352               select @ptnid = partitionid
353               from syspartitions
354               where id = @objid
355                   and indid = @indexid
356                   and name = @ptnname
357   
358               if @ptnid is not null
359               begin
360                   /* If it is valid partition, update syspartitions */
361                   update syspartitions
362                   set segment = @segment
363                   from syspartitions
364                   where id = @objid
365                       and indid = @indexid
366                       and name = @ptnname
367   
368                   if (@@error != 0)
369                   begin
370                       rollback tran updatesys
371                       return (1)
372                   end
373   
374                   goto refresh_pdes
375               end
376           end
377   
378           /*
379           ** We reached here because ptnid is null in all cases. Raise error.
380           ** 19305, "There is no partition named '%1!' for table '%2!', index '%3!'."
381           */
382           raiserror 19305, @ptnname, @tabname, @indexname
383           rollback tran updatesys
384           return (1)
385   
386   refresh_pdes:
387           /* Refresh in-core structure representing this syspartitions row */
388           dbcc refreshpdes(@dbname, @objid, @indexid, @ptnid)
389   
390           if (@@error != 0)
391           begin
392               rollback tran updatesys
393               return (1)
394           end
395   
396           goto commit_tran
397       end
398   
399       /* Get the partition ids of affected index*/
400       declare ptnid_crsr cursor
401       for select partitionid from syspartitions
402       where id = @objid and indid = @indexid
403       for read only
404   
405       update sysindexes
406       set segment = @segment
407       from sysindexes
408       where id = @objid and indid = @indexid
409       plan "(update (i_scan csysindexes sysindexes))"
410   
411       if (@@error != 0)
412       begin
413           rollback tran updatesys
414           return (1)
415       end
416   
417       /*
418       ** We updated a sysindexes row. We also need to update the in-core
419       ** structure representing this sysindexes row as the sysindexes rows
420       ** cache is not a write thru cache.
421       */
422       dbcc refreshides(@dbname, @objid, @indexid, "placeobject")
423   
424       if (@@error != 0)
425       begin
426           rollback tran updatesys
427           return (1)
428       end
429   
430       update syspartitions
431       set segment = @segment
432       from syspartitions
433       where id = @objid and indid = @indexid
434   
435       if (@@error != 0)
436       begin
437           rollback tran updatesys
438           return (1)
439       end
440   
441       /*
442       ** We updated syspartitions row. We also need to update the in-core
443       ** structure representing this syspartitions row.
444       */
445       open ptnid_crsr
446       fetch ptnid_crsr into @ptnid
447       while (@@sqlstatus = 0)
448       begin
449           dbcc refreshpdes(@dbname, @objid, @indexid, @ptnid, "placeobject")
450   
451           if (@@error != 0)
452           begin
453               rollback tran updatesys
454               return (1)
455           end
456           fetch ptnid_crsr into @ptnid
457       end
458       close ptnid_crsr
459       deallocate cursor ptnid_crsr
460   
461       if (@moreupdates = 1)
462       begin
463           declare placementptnid_crsr cursor
464           for select partitionid from syspartitions
465           where id = @objid and indid = @placementidx
466           for read only
467   
468           update sysindexes
469           set segment = @segment
470           from sysindexes
471           where id = @objid and indid = @placementidx
472           plan "(update (i_scan csysindexes sysindexes))"
473   
474           if (@@error != 0)
475           begin
476               rollback tran updatesys
477               return (1)
478           end
479   
480           /*
481           ** We updated a sysindexes row. We also need to update the in-core
482           ** structure representing this sysindexes row as the sysindexes rows
483           ** cache is not a write thru cache.
484           */
485           dbcc refreshides(@dbname, @objid, @placementidx, "placeobject")
486   
487           if (@@error != 0)
488           begin
489               rollback tran updatesys
490               return (1)
491           end
492   
493           update syspartitions
494           set segment = @segment
495           from syspartitions
496           where id = @objid
497               and indid = @placementidx
498   
499           if (@@error != 0)
500           begin
501               rollback tran updatesys
502               return (1)
503           end
504   
505           /*
506           ** We updated syspartitions row. We also need to update the in-core
507           ** structure representing this syspartitions row.
508           */
509           open placementptnid_crsr
510           fetch placementptnid_crsr into @placementptnid
511           while (@@sqlstatus = 0)
512           begin
513               dbcc refreshpdes(@dbname, @objid, @placementidx,
514                   @placementptnid, "placeobject")
515   
516               if (@@error != 0)
517               begin
518                   rollback tran updatesys
519                   return (1)
520               end
521               fetch placementptnid_crsr into @placementptnid
522           end
523           close placementptnid_crsr
524           deallocate cursor placementptnid_crsr
525       end
526   commit_tran:
527       commit tran updatesys
528   
529       dbcc dbrepair(@dbname, remap, NULL, - 1, @segname)
530       /*
531       ** 17735, "'%1!' is now on segment '%2!'."
532       */
533       exec sp_getmessage 17735, @msg output
534       print @msg, @objname, @segname
535   
536       return (0)
537   


exec sp_procxmode 'sp_placeobject', 'AnyMode'
go

Grant Execute on sp_placeobject to public
go
DEFECTS
 MTYP 4 Assignment type mismatch @tabname: varchar(767) = varchar(1023) 64
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
174
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 258
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 270
 TNOI 4 Table with no index sybsystemprocs..syssegments sybsystemprocs..syssegments
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 135
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 176
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 201
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 258
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 270
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 285
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 312
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 322
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 354
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 364
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 402
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 408
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 433
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 465
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 471
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 496
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public sybsystemprocs..sp_placeobject  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..syspartitions  
 MGTP 3 Grant to public sybsystemprocs..syssegments  
 MNER 3 No Error Check should check return value of exec 533
 MUCO 3 Useless Code Useless Brackets 45
 MUCO 3 Useless Code Useless Brackets 77
 MUCO 3 Useless Code Useless Brackets 85
 MUCO 3 Useless Code Useless Brackets 91
 MUCO 3 Useless Code Useless Brackets 97
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 109
 MUCO 3 Useless Code Useless Brackets 115
 MUCO 3 Useless Code Useless Brackets 131
 MUCO 3 Useless Code Useless Brackets 137
 MUCO 3 Useless Code Useless Brackets 141
 MUCO 3 Useless Code Useless Brackets 148
 MUCO 3 Useless Code Useless Brackets 155
 MUCO 3 Useless Code Useless Brackets 161
 MUCO 3 Useless Code Useless Brackets 163
 MUCO 3 Useless Code Useless Brackets 168
 MUCO 3 Useless Code Useless Brackets 183
 MUCO 3 Useless Code Useless Brackets 208
 MUCO 3 Useless Code Useless Brackets 212
 MUCO 3 Useless Code Useless Brackets 216
 MUCO 3 Useless Code Useless Brackets 226
 MUCO 3 Useless Code Useless Brackets 260
 MUCO 3 Useless Code Useless Brackets 272
 MUCO 3 Useless Code Useless Brackets 287
 MUCO 3 Useless Code Useless Brackets 308
 MUCO 3 Useless Code Useless Brackets 330
 MUCO 3 Useless Code Useless Brackets 339
 MUCO 3 Useless Code Useless Brackets 342
 MUCO 3 Useless Code Useless Brackets 345
 MUCO 3 Useless Code Useless Brackets 368
 MUCO 3 Useless Code Useless Brackets 371
 MUCO 3 Useless Code Useless Brackets 384
 MUCO 3 Useless Code Useless Brackets 390
 MUCO 3 Useless Code Useless Brackets 393
 MUCO 3 Useless Code Useless Brackets 411
 MUCO 3 Useless Code Useless Brackets 414
 MUCO 3 Useless Code Useless Brackets 424
 MUCO 3 Useless Code Useless Brackets 427
 MUCO 3 Useless Code Useless Brackets 435
 MUCO 3 Useless Code Useless Brackets 438
 MUCO 3 Useless Code Useless Brackets 447
 MUCO 3 Useless Code Useless Brackets 451
 MUCO 3 Useless Code Useless Brackets 454
 MUCO 3 Useless Code Useless Brackets 461
 MUCO 3 Useless Code Useless Brackets 474
 MUCO 3 Useless Code Useless Brackets 477
 MUCO 3 Useless Code Useless Brackets 487
 MUCO 3 Useless Code Useless Brackets 490
 MUCO 3 Useless Code Useless Brackets 499
 MUCO 3 Useless Code Useless Brackets 502
 MUCO 3 Useless Code Useless Brackets 511
 MUCO 3 Useless Code Useless Brackets 516
 MUCO 3 Useless Code Useless Brackets 519
 MUCO 3 Useless Code Useless Brackets 536
 MUUF 3 Update or Delete with Useless From Clause 319
 MUUF 3 Update or Delete with Useless From Clause 361
 MUUF 3 Update or Delete with Useless From Clause 405
 MUUF 3 Update or Delete with Useless From Clause 430
 MUUF 3 Update or Delete with Useless From Clause 468
 MUUF 3 Update or Delete with Useless From Clause 493
 QAFM 3 Var Assignment from potentially many rows 36
 QAFM 3 Var Assignment from potentially many rows 173
 QAFM 3 Var Assignment from potentially many rows 283
 QAFM 3 Var Assignment from potentially many rows 310
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE plan..."(update (i_scan csysindexes sysindexes))" 405
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE plan..."(update (i_scan csysindexes sysindexes))" 468
 QISO 3 Set isolation level 31
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {id}
201
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {id}
285
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syspartitions.csyspartitions unique clustered
(id, indid, partitionid)
Intersection: {id, indid}
312
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syspartitions.csyspartitions unique clustered
(id, indid, partitionid)
Intersection: {id, indid}
322
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syspartitions.csyspartitions unique clustered
(id, indid, partitionid)
Intersection: {indid, id}
402
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syspartitions.csyspartitions unique clustered
(id, indid, partitionid)
Intersection: {indid, id}
433
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syspartitions.csyspartitions unique clustered
(id, indid, partitionid)
Intersection: {indid, id}
465
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syspartitions.csyspartitions unique clustered
(id, indid, partitionid)
Intersection: {indid, id}
496
 VNRD 3 Variable is not read @dummy 169
 VNRD 3 Variable is not read @procval 190
 CRDO 2 Read Only Cursor Marker (has for read only clause) 401
 CRDO 2 Read Only Cursor Marker (has for read only clause) 464
 MSUB 2 Subquery Marker 134
 MSUB 2 Subquery Marker 175
 MSUB 2 Subquery Marker 200
 MSUB 2 Subquery Marker 219
 MTR1 2 Metrics: Comments Ratio Comments: 35% 2
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 40 = 63dec - 25exi + 2 2
 MTR3 2 Metrics: Query Complexity Complexity: 272 2

DEPENDENCIES
PROCS AND TABLES USED
read_writes table sybsystemprocs..sysindexes  
reads table master..spt_values (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
reads table sybsystemprocs..syssegments  
read_writes table sybsystemprocs..syspartitions  
reads table sybsystemprocs..sysobjects  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
   reads table sybsystemprocs..sysusermessages