Switch to standard view 
  Sybase logo
 
 
 



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


Back to Top
© Copyright 2010, Sybase Inc.