| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_fixindex | | 31 Aug 14 | Defects 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 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 109 110 /* 111 ** check if user has SA role, proc_role will also do auditing 112 ** if required. proc_role will also print error message if required. 113 */ 114 if (proc_role("sa_role") = 0) 115 begin 116 return (1) 117 end 118 119 /* 120 ** Check if there is already an active transaction. It there 121 ** is one then return (do not allow sp_fixindex inside a 122 ** transaction). 123 */ 124 if (@@trancount > 0) 125 begin 126 raiserror 17260, "sp_fixindex" 127 return (1) 128 end 129 130 /* 131 ** Make sure that we are in the database specified 132 ** by @dbname. 133 */ 134 if @dbname != db_name() 135 begin 136 raiserror 18301, @dbname 137 return (1) 138 end 139 140 exec sp_getmessage 17431, @true out 141 exec sp_getmessage 17432, @false out 142 143 if ((@force_option is NOT NULL) 144 and (lower(@force_option) not in ("true", "false", @true, @false))) 145 begin 146 raiserror 19602 147 return (1) 148 end 149 150 /* 151 ** Note on selectivity of dbrepair_flags and reindex_flags. 152 ** 153 ** It is mandatory to put user database in "single user mode" 154 ** and this is not possible for tempdb. Due to this "single user 155 ** mode" requirement is ignored for tempdb USE 1 for reindex 156 ** to ignore the "single user mode" and DO NOT USE 16 for 157 ** dbrepair with REPAIRINDEX option. 158 ** 159 ** The main motivation of this procedure is to rebuild the 160 ** index on the catalogs. It is required to force the index 161 ** rebuild in both (reindex and dbrepair) case. 162 */ 163 164 select @lcl_indid = @indid 165 , @default_ind_val = NULL 166 , @dbcc_dbr_dropi = 1 167 , @dbcc_dbr_cri = 2 168 , @dbcc_dbr_chk_ramboix = 4 169 , @dbcc_dbr_report = 8 170 , @dbcc_dbr_sngusr_mode = 16 171 , @dbcc_ridx_skip_sngusr = 1 172 , @dbcc_ridx_force_rbld = 16 173 , @dbcc_error = 0 174 , @dbcc_cmd = "GENERIC" 175 176 /* 177 ** Initialize the default flags to be passed to DBCC commands 178 */ 179 180 select @reindex_flags = @dbcc_ridx_force_rbld 181 , @dbrepair_flags = (@dbcc_dbr_dropi 182 | @dbcc_dbr_cri 183 | @dbcc_dbr_chk_ramboix 184 | @dbcc_dbr_report 185 | @dbcc_dbr_sngusr_mode) 186 187 if @@trancount = 0 188 begin 189 set chained off 190 end 191 192 set transaction isolation level 1 193 194 /* 195 ** Check whether the current database is tempdb or a user 196 ** tempdb and set appropriate options. 197 ** 198 ** As 'tempdb' can not be put into "single user" mode additional 199 ** flags should be passed to "reindex and dbrepair" for rebuilding 200 ** the catalogs. 201 ** 202 ** @force_option should be used only for tempdb. If this option 203 ** is used for user database return fail. 204 */ 205 206 select @tempdb_mask = number 207 from master.dbo.spt_values 208 where type = "D3" and name = "TEMPDB STATUS MASK" 209 210 if ((@dbname = 'tempdb') 211 OR (exists (select 1 212 from master.dbo.sysdatabases 213 where name = @dbname AND ((status3 & @tempdb_mask) != 0)))) 214 begin 215 216 if (@force_option not in ("true", @true)) 217 begin 218 raiserror 19596, @dbname, "@force_option", "true" 219 return (1) 220 end 221 222 select @reindex_flags = (@dbcc_ridx_force_rbld 223 | @dbcc_ridx_skip_sngusr) 224 , @dbrepair_flags = (@dbcc_dbr_dropi 225 | @dbcc_dbr_cri 226 | @dbcc_dbr_chk_ramboix 227 | @dbcc_dbr_report) 228 end 229 else if (@force_option in ("true", @true)) 230 begin 231 raiserror 19597, @dbname, "@force_option" 232 return (1) 233 end 234 235 select @objtype = type 236 from sysobjects 237 where name = @tabname 238 239 /* check if the table name is valid */ 240 if (@objtype is null) 241 begin 242 raiserror 17461 243 return (1) 244 end 245 246 /* check if the table is a system table */ 247 if (@objtype != "S") 248 begin 249 raiserror 18055 250 return (1) 251 end 252 253 /* 254 ** Explicitly disallow sysindexes, and disallow any 255 ** table that has no indexes. 256 */ 257 if @tabname = "sysindexes" 258 or not exists (select 1 259 from sysindexes 260 where id = object_id(@tabname) 261 and indid > 0) 262 begin 263 raiserror 18056 264 return (1) 265 end 266 267 /* 268 ** Client can specify either @indid or @index_name but not both. 269 ** 270 ** (a) If @indid is valid then rebuild the corresponding 271 ** index. 272 ** (b) If @indid is NULL and @index_name is specified then 273 ** rebuild the index associated with @index_name. 274 ** (c) If both @indid and @index_name are not NULL and if 275 ** @indid is not associated with @index_name then an error 276 ** will be raised. 277 ** index associated with @indid will be rebuilt. 278 ** (d) If both @indid and @index_name are NULL then all the 279 ** indices on @tabname will be rebuilt. 280 */ 281 282 if (@lcl_indid is NOT NULL) 283 begin --{ 284 285 if (@lcl_indid = 0) 286 begin 287 raiserror 19598, @tabname, @tabname, 0 288 return (1) 289 end 290 291 if (@lcl_indid = 255) 292 begin 293 raiserror 19600, "sp_fixindex" 294 return (1) 295 end 296 297 select @lcl_index_name = name 298 from sysindexes 299 where id = object_id(@tabname) and indid = @lcl_indid 300 301 if ((@index_name is NOT NULL) 302 and (@lcl_index_name != @index_name)) 303 begin 304 raiserror 19599 305 return (1) 306 end 307 308 if (@lcl_index_name is NULL) 309 begin 310 raiserror 18091 311 return (1) 312 end 313 314 end --} 315 else if (@index_name is NOT NULL) 316 begin --{ 317 318 /* 319 ** If the index name is given then retrieve the 320 ** index id from SYSINDEXES. 321 */ 322 select @lcl_indid = indid 323 from sysindexes 324 where id = object_id(@tabname) 325 and name = @index_name 326 and indid > 0 and indid < 255 327 328 /* 329 ** If index name is suplied we should get a valid 330 ** index id i.e @lcl_indid should be non NULL 331 */ 332 333 if (@lcl_indid is NULL) 334 begin 335 raiserror 17734, @index_name, @tabname 336 return (1) 337 end 338 end --} 339 340 341 /* 342 ** At this point we are sure that the table has an index 343 ** of requested type, so all there is to be done is to 344 ** set the ramboix bit for this object. 345 */ 346 347 begin tran set_sysobjects_sysstat 348 349 select @ramboix = 4096 350 update sysobjects set sysstat = sysstat | @ramboix 351 where name = @tabname 352 353 if (@@error != 0) 354 begin 355 raiserror 19380, 'sysobjects' 356 rollback set_sysobjects_sysstat 357 return (@@error) 358 end 359 360 commit tran set_sysobjects_sysstat 361 362 checkpoint @dbname 363 364 /* 365 ** If indid is default indid (i.e NULL) then the whole table should 366 ** be rebuilt. Otherwise rebuild only the index associated with 367 ** @lcl_indid. 368 */ 369 if (@lcl_indid is NULL) 370 begin 371 select @dbcc_cmd = "DBCC REINDEX" 372 dbcc reindex(@tabname, @reindex_flags) 373 end 374 else 375 begin 376 select @dbcc_cmd = "DBCC DBREPAIR" 377 dbcc dbrepair(@dbname, REPAIRINDEX, @tabname, 378 @lcl_indid, @dbrepair_flags) 379 end 380 381 /* 382 ** Remember the result of DBCC commands. 383 */ 384 select @dbcc_error = @@error 385 386 /* 387 ** Processing successfully done, so turn off the ramboix 388 ** bit, after telling the user that all is fine. 389 */ 390 391 begin tran unset_sysobjects_sysstat 392 393 update sysobjects set sysstat = sysstat & ~ @ramboix 394 where name = @tabname 395 396 if (@@error != 0) 397 begin 398 raiserror 19380, 'sysobjects' 399 rollback unset_sysobjects_sysstat 400 return (@@error) 401 end 402 403 commit tran unset_sysobjects_sysstat 404 405 checkpoint @dbname 406 407 if (@dbcc_error != 0) 408 begin 409 raiserror 19601, "sp_fxindex", @dbcc_cmd, @tabname 410 return (@dbcc_error) 411 end 412 413 return (0) 414 -- } 415
exec sp_procxmode 'sp_fixindex', 'AnyMode' go Grant Execute on sp_fixindex to public go
| DEPENDENCIES |
| PROCS AND TABLES USED reads table master..spt_values (1)  reads table master..sysdatabases (1) read_writes table sybsystemprocs..sysobjects 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) reads table sybsystemprocs..sysindexes |
