View source for Find and fix suspected indexes in a database

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

[[Category:ASE]]