Find and fix suspected indexes in a database

From SybaseWiki
Jump to: navigation, search

When a database is loaded into a server it is sometimes needed to run dbcc fixindex. An error message like this is reported in the errorlog:

WARNING: ***************************
While loading database 'test', ASE has
  detected that the following indexes may be invalid,
  since they ...
    (a) were created under a different sort order
  ID and/or character set ID than the one(s)
currently running on this ASE
  (sortord ID = 50 and charset ID = 1), and...
    (b) have CHAR or VARCHAR columns in their keys.
  The suspected indexes are:
  systypes.csystypes (objid = 4, indid = 3, soid = 50, csid = 190)
  sysreferences.csysreferences (objid = 16, indid = 4, soid = 50, csid = 190)
  sysattributes.csysattributes (objid = 21, indid = 3, soid = 50, csid = 190)
  sysxtypes.ncsysxtypes (objid = 25, indid = 3, soid = 50, csid = 190)
  sysjars.ncsysjars (objid = 26, indid = 3, soid = 50, csid = 190)
  customer.pk_customer (objid = 158620577, indid = 1, soid = 50, csid = 190)
  address.adres_zip (objid = 290097043, indid = 2, soid = 50, csid = 190)

The following stored procedure find the suspected indexes and tries to repair them.

create proc sp_dbcc_reindex
as

declare @id int
declare myCursor cursor for
  select so.id
    from  sysobjects so
    where exists(
      select 1
        from sysindexes si
        where so.id = si.id
        and si.status & 32768 = 32768)
  for read only

open myCursor

if @@error != 0
  return  1

fetch myCursor into @id

while @@sqlstatus = 0
begin
  dbcc reindex(@id)

  fetch myCursor into @id
end

close myCursor
deallocate cursor myCursor

return  0