DatabaseProcApplicationCreatedLinks
sybsystemprocssp_placeobject  31 Aug 14Defects 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
DEFECTS
 MTYP 4 Assignment type mismatch @tabname: varchar(767) = varchar(1023) 84
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
156
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 244
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 253
 TNOI 4 Table with no index sybsystemprocs..syssegments sybsystemprocs..syssegments
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 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 419
 MUCO 3 Useless Code Useless Brackets 65
 MUCO 3 Useless Code Useless Brackets 95
 MUCO 3 Useless Code Useless Brackets 101
 MUCO 3 Useless Code Useless Brackets 107
 MUCO 3 Useless Code Useless Brackets 113
 MUCO 3 Useless Code Useless Brackets 119
 MUCO 3 Useless Code Useless Brackets 125
 MUCO 3 Useless Code Useless Brackets 131
 MUCO 3 Useless Code Useless Brackets 144
 MUCO 3 Useless Code Useless Brackets 150
 MUCO 3 Useless Code Useless Brackets 165
 MUCO 3 Useless Code Useless Brackets 190
 MUCO 3 Useless Code Useless Brackets 194
 MUCO 3 Useless Code Useless Brackets 198
 MUCO 3 Useless Code Useless Brackets 208
 MUCO 3 Useless Code Useless Brackets 255
 MUCO 3 Useless Code Useless Brackets 270
 MUCO 3 Useless Code Useless Brackets 297
 MUCO 3 Useless Code Useless Brackets 300
 MUCO 3 Useless Code Useless Brackets 310
 MUCO 3 Useless Code Useless Brackets 313
 MUCO 3 Useless Code Useless Brackets 321
 MUCO 3 Useless Code Useless Brackets 324
 MUCO 3 Useless Code Useless Brackets 333
 MUCO 3 Useless Code Useless Brackets 337
 MUCO 3 Useless Code Useless Brackets 340
 MUCO 3 Useless Code Useless Brackets 347
 MUCO 3 Useless Code Useless Brackets 360
 MUCO 3 Useless Code Useless Brackets 363
 MUCO 3 Useless Code Useless Brackets 373
 MUCO 3 Useless Code Useless Brackets 376
 MUCO 3 Useless Code Useless Brackets 385
 MUCO 3 Useless Code Useless Brackets 388
 MUCO 3 Useless Code Useless Brackets 397
 MUCO 3 Useless Code Useless Brackets 402
 MUCO 3 Useless Code Useless Brackets 405
 MUCO 3 Useless Code Useless Brackets 422
 MUUF 3 Update or Delete with Useless From Clause 291
 MUUF 3 Update or Delete with Useless From Clause 316
 MUUF 3 Update or Delete with Useless From Clause 354
 MUUF 3 Update or Delete with Useless From Clause 379
 QAFM 3 Var Assignment from potentially many rows 56
 QAFM 3 Var Assignment from potentially many rows 155
 QAFM 3 Var Assignment from potentially many rows 266
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE plan..."(update (i_scan csysindexes sysindexes))" 291
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE plan..."(update (i_scan csysindexes sysindexes))" 354
 QISO 3 Set isolation level 51
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {id}
183
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {id}
268
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syspartitions.csyspartitions unique clustered
(id, indid, partitionid)
Intersection: {indid, id}
288
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syspartitions.csyspartitions unique clustered
(id, indid, partitionid)
Intersection: {indid, id}
319
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syspartitions.csyspartitions unique clustered
(id, indid, partitionid)
Intersection: {indid, id}
351
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syspartitions.csyspartitions unique clustered
(id, indid, partitionid)
Intersection: {indid, id}
382
 VNRD 3 Variable is not read @procval 172
 CRDO 2 Read Only Cursor Marker (has for read only clause) 287
 CRDO 2 Read Only Cursor Marker (has for read only clause) 350
 MSUB 2 Subquery Marker 141
 MSUB 2 Subquery Marker 157
 MSUB 2 Subquery Marker 182
 MSUB 2 Subquery Marker 201
 MTR1 2 Metrics: Comments Ratio Comments: 45% 30
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 27 = 44dec - 19exi + 2 30
 MTR3 2 Metrics: Query Complexity Complexity: 206 30

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)