Manually Dropping a Corrupt Table and its Related Objects

Steps for Dropping a Table

---

Note: The following steps include an undocumented and unsupported dbcc option, extentzap. Use it at your own risk. Using it requires both sa_role and sybase_ts_role permissions.

---

Before you begin, make sure the table is not in use. Then follow these steps:

1. Turn on support for making changes to tables:
   
   sp_configure "allow updates to system tables", 1
2. Use the database that contains the corrupt table:
   
   use database-name
3. Run the following commands and write down the ID numbers; you will need these later:
   
   For the database ID:
   
   select db_id('database-name')
   For the ID of the corrupt table:
   
   select id from sysobjects where name = 'bad-table-name'
   For the table's index IDs:
   
   select indid from sysindexes where id = bad-table-id
4. The following step is optional but highly recommended. Mark the start of a user-defined transaction:
   
   begin tran
   
5. Delete all system catalog information for the object, including any object and procedure dependencies by creating and using all of this short script:
   

   declare @obj int
   select @obj = id from sysobjects where name = bad-table-namedelete syscolumns where id = @obj
   delete sysindexes where id = @obj
   delete sysobjects where id in (select constrid from sysconstraints where tableid  = @obj)
   delete sysdepends where depid = @obj
   delete syskeys where id = @obj
   delete syskeys where depid = @obj
   delete sysprotects where id = @obj
   delete sysconstraints where tableid = @obj
   delete sysreferences where tableid = @obj
   delete sysattributes where object = @obj
   delete syspartitions where id = @obj
   delete sysstatistics where id = @obj
   delete systabstats where id = @obj
   delete syscomments where id in (select id from sysobjects where deltrig = @obj)
   delete syscomments where id in (select id from sysobjects where instrig = @obj)
   delete syscomments where id in (select id from sysobjects where updtrig = @obj)
   delete sysprocedures where id in (select id from sysobjects where deltrig = @obj)
   delete sysprocedures where id in (select id from sysobjects where instrig = @obj)
   delete sysobjects where deltrig = @obj
   delete sysobjects where instrig = @obj
   delete sysobjects where updtrig = @obj
   delete sysobjects where id = @obj 
   
   /* If you are using Adaptive Server version 15.0 or newer, */
   /* you will need to add 3 more system tables to the script */
   delete sysstatistics where id = @obj
   delete systabstats where id = @obj
   delete syspartitionkeys where id = @obj

6. Note: If you make a mistake, cancel the transaction using the rollback command; and then correct and submit the script again.
7. Mark the end of the transaction:
   

   commit tran

8. Prepare to run dbcc, using the undocumented and unsupported option extentzap. Make the database read only by submitting each of the following commands:

   use master
   sp_dboption database-name, 'read only', true
   use database-name
   checkpoint

   ---

   WARNING: When you execute dbcc extentzap, it clears all extents for a given object ID and indid. The only way to recover the data is to use a database backup.
   

   ---

9. Assuming that you have the required sa_role and sybase_ts_role permissions, run dbcc extentzap twice for each index - once with a final parameter of "0" and again with a final parameter of "1". If the table uses and ALLPAGES lock scheme and has a clustered index, you also need to delete extents on index 0, even though that indid has no sysindexes entry. Use the following syntax, being very careful to use the correct object ID, that is, the object ID of the bad table:
   

   dbcc traceon(3604)
   
    /* to see the errors */
    
   dbcc extentzap (database-id, object-id, index-id,  0)dbcc extentzap (database-id, object-id, index-id,  1)    

10. Clean up using the following commands:
    

    use mastersp_configure "allow updates to system tables", 0sp_dboption database-name, 'read only', falseuse database-namecheckpoint