Q2.4: How to manually drop a table

Here's a list of instructions to follow when trying to drop a corrupt table:

1.     sp_configure allow, 1
       go
       reconfigure with override
       go
       

2. Write db_id down.

       use db_name
       go
       select db_id() 
       go
       

3. Write down the id of the bad_table:

       select id from sysobjects where name = bad_table_name
       go
       

4. You will need these index IDs to run dbcc extentzap. Also, remember that if the table has a clustered index you will need to run extentzap on index "0", even though there is no sysindexes entry for that indid.

       select indid from sysindexes where id = table_id
       go
       

5. This is not required but a good idea:

       begin transaction
       go 
       

6. Type in this short script, this gets rid of all system catalog information for the object, including any object and procedure dependencies that may be present.

   Some of the entries are unnecessary but better safe than sorry.

        declare @obj int
        select @obj = id from sysobjects where name = 
        delete syscolumns where id = @obj
        delete sysindexes where id = @obj
        delete sysobjects where id = @obj
        delete sysprocedures where id in
   	    (select id from sysdepends where depid = @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 sysdepends where id = @obj
        go
        

7. Just do it!

       commit transaction
       go
       

8. Gather information to run dbcc extentzap:

       use master
       go
       sp_dboption db_name, read, true
       go
       use db_name
       go
       checkpoint
       go
       

9. Run dbcc extentzap once for each index (including index 0, the data level) that you got from above:

       use master
       go
       dbcc traceon (3604)
       go
       dbcc extentzap (db_id, obj_id, indx_id, 0)
       go
       dbcc extentzap (db_id, obj_id, indx_id, 1)
       go
       

   Notice that extentzap runs twice for each index. This is because the last parameter (the sort bit) might be 0 or 1 for each index, and you want to be absolutely sure you clean them all out.

10. Clean up after yourself.

        sp_dboption db_name, read, false
        go
        use db_name
        go
        checkpoint
        go
        sp_configure allow, 0
        go
        reconfigure with override
        go