Tuesday, March 27, 2012

Dropping An Indexed Column

I have inherited a table with dozens of columns that I no longer want. I want to drop these columns.

So I tried
"ALTER TABLE mydata DROP BLOCK_ID"

and it get an error of: cannot delete a field that is part of an index. How do I get around this?

(BLOCK_ID is the field name of my indexed column)
(The non-indexed ones drop fine.)First remove the column you want to drop from all the indexes that refer it. If there are indexes that refer only that column just drop those. Then you can drop the column.

Cheers,
Suren.|||Yes, I get that I have to drop the index(es) -- but how do I find out which indexes this column is in?

I'm building up to write some scripts to automatically drop a long-list of unwanted columns - how does one go about figuring out what index a field is in? And/or is there a sql way of saying "drop this column and it's indexes" ?|||Well to do that the mist easiest way is to use a graphical tool that organise indexes unser each table and to go through the index and remove the coloms.

If you are thinking of writing scripts then you should select from catalog tables such as user_indexes and user_inx_cols. I think I got the names correct.

No comments:

Post a Comment