Skip to Content
SAP Adaptive Server Enterprise

How to Manually Drop a Corrupt Table in ASE

Tags:

Steps for Dropping a Table


Note: The following steps involve an undocumented command DBCC extentzap, which deallocates the extents for an object.  Using extentzap requires both sa_role and sybase_ts_role permissions.


This process can be used when the DROP TABLE command fails.

Before you begin, it is a good idea to make sure you have a recent backup of the database.

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 (if you don't already have the id from an error message):

    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 in case you find you have made an error.
    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-name
    delete 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 sysprocedures where id in (select id from sysobjects where updtrig= @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

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

Mark the end of the transaction:

commit tran


6. 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 from a database backup or other external data source.

7. 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 the 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:

set switch on 3604  /* to direct output to client so you can see any errors */
dbcc extentzap (database-id, object-id, index-id, 0)
dbcc extentzap (database-id, object-id, index-id, 1)

8. Clean up using the following commands:

use master

sp_configure "allow updates to system tables", 0
sp_dboption database-name, 'read only', false
use database-name
checkpoint