1. sp_configure “allow updates”, 1
go
or
reconfigure with override ( if System X)
go
2. Use the database; get its dbid [select db_id()] and write it
down for reference.
3. select id from sysobjects where name = <bad-table-name>
go
… write that down, too.
4. select indid from sysindexes where id = <table-id>
go
… 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.
5. begin transaction
go
… not required, but a *real*good*idea*.
6. Type in 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 = @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
…This gets rid of all system catalog information for the
object,
including any object and procedure dependencies that may be
present.
Some of these lines may be unnecessary; you should type them in
anyway just for the exercise
7. commit transaction
go
(unless you made a mistake in step 6, in which case rollback.)
8. Prepare to run dbcc extentzap:
use master
go
sp_dboption <db-name>, “read”, true
go
use <db-name>
go
checkpoint
go
(Each of the above must be given as a separate batch — that is,
type “go” after every line.)
sp_role “grant”, sybase_ts_role, “sa”
go
set role “sybase_ts_role” on
go
9. Run dbcc extentzap once for EACH index (including index 0, the
data
level) that you got from step 4 above:
**********
The following commands are very dangerous commands
use them with care because, if you give the wrong object id,
all data for that object will be lost forever. You want to
make sure that the object id is the id of the bad table and
not one of your good objects
**********
dbcc traceon(3604)
go
/* lets you see errors */
dbcc extentzap( <db-id>, <object-id>, <index-id>, 0)
go
dbcc extentzap( <db-id>, <object-id>, <index-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:
use master
go
sp_dboption <db-name>, “read”, false
go
sp_configure allow,0
go
reconfigure ( if System X)
go
use <db-name>
go
checkpoint
go