Sybase Business Intelligence Solutions - Database Management, Data Warehousing Software, Mobile Enterprise Applications and Messaging
Sybase Brand Color Bar
delete

Search for    in all of Sybase.com
view all search results right arrow
  blank
 
 
 
 
 
 
 
 
 
 

 
 
CLICK TO EXPAND NAVIGATION
CLICK TO EXPAND NAVIGATION
 
 
 
 
Support > Technical Documents > Document Types > Technote > Manually Dropping a Corrupt Table and its Related ...

Manually Dropping a Corrupt Table and its Related Objects

If you cannot drop a corrupt table using the drop table command, you can drop it manually using the steps in this technote.
 
RSS Feed
 
 
 

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-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 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 master
    sp_configure "allow updates to system tables", 0
    sp_dboption database-name, 'read only', false
    use database-name
    checkpoint

 

DOCUMENT ATTRIBUTES
Last Revised: Apr 20, 2009
Product: SQL Server, Adaptive Server Enterprise
Technical Topics: Database Admin, Troubleshooting
  
Business or Technical: Technical
Content Id: 20230
Infotype: Technote
 
 
 

 
© Copyright 2014, Sybase Inc. - v 7.6 Home / Contact Us / Help / Jobs / Legal / Privacy / Code of Ethics