Occasionally you may find that after issuing a drop table command that the ASE crashed and consequently the table didn't drop entirely. Sure you can't see it but that sucker is still floating around somewhere.

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