DatabaseProcApplicationCreatedLinks
sybsystemprocssp_fixindex  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** Generated by spgenmsgs.pl on Tue Oct 31 22:45:51 2006 
4     */
5     /*
6     ** raiserror Messages for fixindex [Total 15]
7     **
8     ** 17260, "Can't run %1! from within a transaction."
9     ** 17461, "Object does not exist in this database."
10    ** 17734, "There is no index named '%1!' for table '%2!'."
11    ** 18055, "Procedure should be used on system tables only."
12    ** 18056, "Cannot re-create index on this table."
13    ** 18091, "The target index does not exist."
14    ** 18301, "Database name '%1!' is not the current database."
15    ** 19380, "Error in accessing the table %1!."
16    ** 19596, "Database '%1!' can not be put into single user mode as it is a temporary database. Set %2! to '%3!' for rebuilding the index."
17    ** 19597, "Database '%1!' is a user database. %2! is only used for temporary database."
18    ** 19598, "Index name '%2!' (ID = %3!) is not the name of an index on '%1!'. Use a valid index name or index id."
19    ** 19599, "You must specify either index name or index id but not both."
20    ** 19600, "%1! cannot be used to rebuild a text/image column."
21    ** 19601, "In procedure '%1!', %2! command failed to rebuild the index on table '%3!'."
22    ** 19602, "sp_fixindex database, systemcatalog [, ind_id | null] [,index_name | null] [, true | false]."
23    */
24    /*
25    ** sp_getmessage Messages for fixindex [Total 3]
26    **
27    ** 17431, "true"
28    ** 17432, "false"
29    ** 18336, "Permission denied. Your curwrite label must be set at the hurdle of the affected database."
30    */
31    /*
32    ** End spgenmsgs.pl output.
33    */
34    /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
35    /*	4.8	1.1	06/14/90	sproc/src/fixindex */
36    /*
37    **	Description:	allow the SA to force a drop and then a
38    **			create index on system catalogs.
39    **
40    **	Usage:	
41    **
42    **	sp_fixindex  database, systemcatalog [, ind_id | null] 
43    **			[,index_name | null] [, true | false]
44    **
45    ** Note on flags passed to reindex and dbrepair
46    ** ============================================
47    ** 	REINDEX will take the following flags (options) 
48    **	and it is an integer (reindex_flags).
49    **
50    **		1	Do not force single user mode for rebuild of 
51    **			system table's index.
52    **		2	Skip rebuild of APL clustered index.
53    **		4	Do not raise exception during checktable.
54    **		8	Do not print dbcc complete message.
55    **		16	Force rebuild of indexes including APL clustered idx.
56    **
57    **	The following options are used by DBREPAIR 
58    **	with REPAIRINDEX (dbrepair_flags).
59    **
60    **		1	Drop system table index
61    **		2	Create system table index
62    **		4	Check for special bit that marks the object to be
63    **			re-indexed. This bit is read from SYSOBJECTS. If
64    **			this option is set and the special bit is missing
65    **			in SYSOBJECTS then index will not be rebuilt.
66    **		8	Reports the information about the number of rows and
67    **			pages that are going to be processed. 
68    **		16	Check the database is already in "single user" mode
69    **			for rebuilding the index. This option should not be
70    **			used for rebuilding indexes in tempdb.
71    **		32	Check index (using checktable) to decide on the
72    **			index rebuild. If checktable returns TRUE then 
73    **			DBREPAIR will not rebuilt any index.
74    **
75    ** Note about the @force_option
76    ** ============================
77    **	@force_option is used to rebuild the catalog indices in temporary 
78    **	databases. It will take either "false" (for user database) or 
79    **	"true" (for tempdb)
80    */
81    
82    create or replace procedure sp_fixindex
83        @dbname varchar(255),
84        @tabname varchar(255), /* system table name */
85        @indid int = NULL, /* index id value    */
86        @index_name varchar(255) = NULL,
87        @force_option varchar(10) = NULL
88    as --{
89        declare @ramboix smallint /* mask for rambo indx*/
90            , @objtype char(2)
91            , @lcl_indid int
92            , @reindex_flags int
93            , @dbrepair_flags int
94            , @default_ind_val int
95            , @dbcc_dbr_dropi int
96            , @dbcc_dbr_cri int
97            , @dbcc_dbr_chk_ramboix int
98            , @dbcc_dbr_report int
99            , @dbcc_dbr_sngusr_mode int
100           , @dbcc_ridx_skip_sngusr int
101           , @dbcc_ridx_force_rbld int
102           , @dbcc_error int
103           , @tempdb_mask int
104           , @lcl_index_name varchar(255)
105           , @dbcc_cmd varchar(255)
106           , @true varchar(255)
107           , @false varchar(255)
108           , @nullarg char(1)
109           , @dummy int
110           , @status int
111           , @gp_enabled int
112   
113   
114   
115       /*
116       ** Check if there is already an active transaction. It there
117       ** is one then return (do not allow sp_fixindex inside a
118       ** transaction).
119       */
120       if (@@trancount > 0)
121       begin
122           raiserror 17260, "sp_fixindex"
123           return (1)
124       end
125   
126   
127       /* 
128       ** If granular permissions is not enabled then sa_role is required.
129       ** If granular permissions is enabled then the permission 
130       ** 'own database' is required.  proc_role and proc_auditperm will also 
131       ** do auditing if required. Both will also print error message if 
132       ** required.
133       */
134   
135       select @nullarg = NULL
136       execute @status = sp_aux_checkroleperm "sa_role", "own database",
137           @dbname, @gp_enabled output
138   
139       /* For Auditing */
140       if (@gp_enabled = 0)
141       begin
142           if (proc_role("sa_role") = 0)
143               return (1)
144       end
145       else
146       begin
147           select @dummy = proc_auditperm("own database", @status, @dbname)
148       end
149   
150       if (@status != 0)
151           return (1)
152   
153       /*
154       ** Make sure that we are in the database specified
155       ** by @dbname.
156       */
157       if @dbname != db_name()
158       begin
159           raiserror 18301, @dbname
160           return (1)
161       end
162   
163       exec sp_getmessage 17431, @true out
164       exec sp_getmessage 17432, @false out
165   
166       if ((@force_option is NOT NULL)
167               and (lower(@force_option) not in ("true", "false", @true, @false)))
168       begin
169           raiserror 19602
170           return (1)
171       end
172   
173       /*
174       ** Note on selectivity of dbrepair_flags and reindex_flags.
175       **
176       ** It is mandatory to put user database in "single user mode"
177       ** and this is not possible for tempdb. Due to this "single user
178       ** mode" requirement is ignored for tempdb USE 1 for reindex
179       ** to ignore the "single user mode" and DO NOT USE 16 for
180       ** dbrepair with REPAIRINDEX option.
181       **
182       ** The main motivation of this procedure is to rebuild the
183       ** index on the catalogs. It is required to force the index
184       ** rebuild in both (reindex and dbrepair) case.
185       */
186   
187       select @lcl_indid = @indid
188           , @default_ind_val = NULL
189           , @dbcc_dbr_dropi = 1
190           , @dbcc_dbr_cri = 2
191           , @dbcc_dbr_chk_ramboix = 4
192           , @dbcc_dbr_report = 8
193           , @dbcc_dbr_sngusr_mode = 16
194           , @dbcc_ridx_skip_sngusr = 1
195           , @dbcc_ridx_force_rbld = 16
196           , @dbcc_error = 0
197           , @dbcc_cmd = "GENERIC"
198   
199       /*
200       ** Initialize the default flags to be passed to DBCC commands
201       */
202   
203       select @reindex_flags = @dbcc_ridx_force_rbld
204           , @dbrepair_flags = (@dbcc_dbr_dropi
205           | @dbcc_dbr_cri
206           | @dbcc_dbr_chk_ramboix
207           | @dbcc_dbr_report
208           | @dbcc_dbr_sngusr_mode)
209   
210       if @@trancount = 0
211       begin
212           set chained off
213       end
214   
215       set transaction isolation level 1
216   
217       /*
218       ** Check whether the current database is tempdb or a user
219       ** tempdb and set appropriate options. 
220       **
221       ** As 'tempdb' can not be put into "single user" mode additional
222       ** flags should be passed to "reindex and dbrepair" for rebuilding 
223       ** the catalogs.
224       **
225       ** @force_option should be used only for tempdb. If this option
226       ** is used for user database return fail.
227       */
228   
229       select @tempdb_mask = number
230       from master.dbo.spt_values
231       where type = "D3" and name = "TEMPDB STATUS MASK"
232   
233       if ((@dbname = 'tempdb')
234               OR (exists (select 1
235                       from master.dbo.sysdatabases
236                       where name = @dbname AND ((status3 & @tempdb_mask) != 0))))
237       begin
238   
239           if (@force_option not in ("true", @true))
240           begin
241               raiserror 19596, @dbname, "@force_option", "true"
242               return (1)
243           end
244   
245           select @reindex_flags = (@dbcc_ridx_force_rbld
246               | @dbcc_ridx_skip_sngusr)
247               , @dbrepair_flags = (@dbcc_dbr_dropi
248               | @dbcc_dbr_cri
249               | @dbcc_dbr_chk_ramboix
250               | @dbcc_dbr_report)
251       end
252       else if (@force_option in ("true", @true))
253       begin
254           raiserror 19597, @dbname, "@force_option"
255           return (1)
256       end
257   
258       select @objtype = type
259       from sysobjects
260       where name = @tabname
261   
262       /* check if the table name is valid */
263       if (@objtype is null)
264       begin
265           raiserror 17461
266           return (1)
267       end
268   
269       /* check if the table is a system table */
270       if (@objtype != "S")
271       begin
272           raiserror 18055
273           return (1)
274       end
275   
276       /*
277       ** Explicitly disallow sysindexes, and disallow any
278       ** table that has no indexes.
279       */
280       if @tabname = "sysindexes"
281           or not exists (select 1
282               from sysindexes
283               where id = object_id(@tabname)
284                   and indid > 0)
285       begin
286           raiserror 18056
287           return (1)
288       end
289   
290       /*
291       ** Client can specify either @indid or @index_name but not both.
292       **
293       **	(a) If @indid is valid then rebuild the corresponding
294       **	    index.
295       **	(b) If @indid is NULL and @index_name is specified then
296       **	    rebuild the index associated with @index_name.
297       **	(c) If both @indid and @index_name are not NULL and if
298       **	    @indid is not associated with @index_name then an error
299       **	    will be raised.
300       **	    index associated with @indid will be rebuilt.
301       **	(d) If both @indid and @index_name are NULL then all the
302       **	    indices on @tabname will be rebuilt.
303       */
304   
305       if (@lcl_indid is NOT NULL)
306       begin --{
307   
308           if (@lcl_indid = 0)
309           begin
310               raiserror 19598, @tabname, @tabname, 0
311               return (1)
312           end
313   
314           if (@lcl_indid = 255)
315           begin
316               raiserror 19600, "sp_fixindex"
317               return (1)
318           end
319   
320           select @lcl_index_name = name
321           from sysindexes
322           where id = object_id(@tabname) and indid = @lcl_indid
323   
324           if ((@index_name is NOT NULL)
325                   and (@lcl_index_name != @index_name))
326           begin
327               raiserror 19599
328               return (1)
329           end
330   
331           if (@lcl_index_name is NULL)
332           begin
333               raiserror 18091
334               return (1)
335           end
336   
337       end --}
338       else if (@index_name is NOT NULL)
339       begin --{
340   
341           /*
342           ** If the index name is given then retrieve the
343           ** index id from SYSINDEXES.
344           */
345           select @lcl_indid = indid
346           from sysindexes
347           where id = object_id(@tabname)
348               and name = @index_name
349               and indid > 0 and indid < 255
350   
351           /*
352           ** If index name is suplied we should get a valid
353           ** index id i.e @lcl_indid should be non NULL
354           */
355   
356           if (@lcl_indid is NULL)
357           begin
358               raiserror 17734, @index_name, @tabname
359               return (1)
360           end
361       end --}
362   
363   
364       /*
365       **	At this point we are sure that the table has an index
366       **	of requested type, so all there is to be done is to
367       **      set the ramboix bit for this object.
368       */
369   
370       begin tran set_sysobjects_sysstat
371   
372       select @ramboix = 4096
373       update sysobjects set sysstat = sysstat | @ramboix
374       where name = @tabname
375   
376       if (@@error != 0)
377       begin
378           raiserror 19380, 'sysobjects'
379           rollback set_sysobjects_sysstat
380           return (@@error)
381       end
382   
383       commit tran set_sysobjects_sysstat
384   
385       checkpoint @dbname
386   
387       /*
388       ** If indid is default indid (i.e NULL) then the whole table should
389       ** be rebuilt. Otherwise rebuild only the index associated with
390       ** @lcl_indid.
391       */
392       if (@lcl_indid is NULL)
393       begin
394           select @dbcc_cmd = "DBCC REINDEX"
395           dbcc reindex(@tabname, @reindex_flags)
396       end
397       else
398       begin
399           select @dbcc_cmd = "DBCC DBREPAIR"
400           dbcc dbrepair(@dbname, REPAIRINDEX, @tabname,
401               @lcl_indid, @dbrepair_flags)
402       end
403   
404       /*
405       ** Remember the result of DBCC commands.
406       */
407       select @dbcc_error = @@error
408   
409       /*
410       **	Processing successfully done, so turn off the ramboix
411       **	bit, after telling the user that all is fine.
412       */
413   
414       begin tran unset_sysobjects_sysstat
415   
416       update sysobjects set sysstat = sysstat & ~ @ramboix
417       where name = @tabname
418   
419       if (@@error != 0)
420       begin
421           raiserror 19380, 'sysobjects'
422           rollback unset_sysobjects_sysstat
423           return (@@error)
424       end
425   
426       commit tran unset_sysobjects_sysstat
427   
428       checkpoint @dbname
429   
430       if (@dbcc_error != 0)
431       begin
432           raiserror 19601, "sp_fxindex", @dbcc_cmd, @tabname
433           return (@dbcc_error)
434       end
435   
436       return (0)
437   -- }
438   


exec sp_procxmode 'sp_fixindex', 'AnyMode'
go

Grant Execute on sp_fixindex to public
go
DEFECTS
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
231
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 284
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 322
 QTYP 4 Comparison type mismatch smallint = int 322
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 349
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 283
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 322
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 347
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public sybsystemprocs..sp_fixindex  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check return value of exec 163
 MNER 3 No Error Check should check return value of exec 164
 MUCO 3 Useless Code Useless Brackets 120
 MUCO 3 Useless Code Useless Brackets 123
 MUCO 3 Useless Code Useless Brackets 140
 MUCO 3 Useless Code Useless Brackets 142
 MUCO 3 Useless Code Useless Brackets 143
 MUCO 3 Useless Code Useless Brackets 150
 MUCO 3 Useless Code Useless Brackets 151
 MUCO 3 Useless Code Useless Brackets 160
 MUCO 3 Useless Code Useless Brackets 166
 MUCO 3 Useless Code Useless Brackets 170
 MUCO 3 Useless Code Useless Brackets 233
 MUCO 3 Useless Code Useless Brackets 239
 MUCO 3 Useless Code Useless Brackets 242
 MUCO 3 Useless Code Useless Brackets 252
 MUCO 3 Useless Code Useless Brackets 255
 MUCO 3 Useless Code Useless Brackets 263
 MUCO 3 Useless Code Useless Brackets 266
 MUCO 3 Useless Code Useless Brackets 270
 MUCO 3 Useless Code Useless Brackets 273
 MUCO 3 Useless Code Useless Brackets 287
 MUCO 3 Useless Code Useless Brackets 305
 MUCO 3 Useless Code Useless Brackets 308
 MUCO 3 Useless Code Useless Brackets 311
 MUCO 3 Useless Code Useless Brackets 314
 MUCO 3 Useless Code Useless Brackets 317
 MUCO 3 Useless Code Useless Brackets 324
 MUCO 3 Useless Code Useless Brackets 328
 MUCO 3 Useless Code Useless Brackets 331
 MUCO 3 Useless Code Useless Brackets 334
 MUCO 3 Useless Code Useless Brackets 338
 MUCO 3 Useless Code Useless Brackets 356
 MUCO 3 Useless Code Useless Brackets 359
 MUCO 3 Useless Code Useless Brackets 376
 MUCO 3 Useless Code Useless Brackets 380
 MUCO 3 Useless Code Useless Brackets 392
 MUCO 3 Useless Code Useless Brackets 419
 MUCO 3 Useless Code Useless Brackets 423
 MUCO 3 Useless Code Useless Brackets 430
 MUCO 3 Useless Code Useless Brackets 433
 MUCO 3 Useless Code Useless Brackets 436
 QAFM 3 Var Assignment from potentially many rows 229
 QAFM 3 Var Assignment from potentially many rows 258
 QISO 3 Set isolation level 215
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
260
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
374
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
417
 VNRD 3 Variable is not read @nullarg 135
 VNRD 3 Variable is not read @dummy 147
 VNRD 3 Variable is not read @default_ind_val 188
 MSUB 2 Subquery Marker 234
 MSUB 2 Subquery Marker 281
 MTR1 2 Metrics: Comments Ratio Comments: 53% 82
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 20 = 36dec - 18exi + 2 82
 MTR3 2 Metrics: Query Complexity Complexity: 142 82

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