Find and fix suspected indexes in a database
Revision as of 16:47, 31 May 2006 by Psap
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 @name varchar(255) declare myCursor cursor for select so.name 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 @name while @@sqlstatus = 0 begin dbcc reindex(@name) fetch myCursor into @name end close myCursor deallocate cursor myCursor return 0