Difference between revisions of "Find and fix suspected indexes in a database"

From SybaseWiki
Jump to: navigation, search
 
m
 
(One intermediate revision by the same user not shown)
Line 24: Line 24:
 
  as
 
  as
 
   
 
   
  declare @name varchar(255)
+
  declare @id int
 
  declare myCursor cursor for
 
  declare myCursor cursor for
   select so.name
+
   select so.id
 
     from  sysobjects so
 
     from  sysobjects so
 
     where exists(
 
     where exists(
Line 40: Line 40:
 
   return  1
 
   return  1
 
   
 
   
  fetch myCursor into @name
+
  fetch myCursor into @id
 
   
 
   
 
  while @@sqlstatus = 0
 
  while @@sqlstatus = 0
 
  begin
 
  begin
   dbcc reindex(@name)
+
   dbcc reindex(@id)
 
   
 
   
   fetch myCursor into @name
+
   fetch myCursor into @id
 
  end
 
  end
 
   
 
   
Line 53: Line 53:
 
   
 
   
 
  return  0
 
  return  0
 +
 +
[[Category:ASE]]

Latest revision as of 14:19, 1 June 2006

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