How to remove a column from a Sybase Table? 1

[rchandr3]

I added a column using ALTER TABLE... Now I dont need that... How to remove the column?

Thanks a lot for your patience!

 

[sybaseguru]

Depending on the version of Sybase. If you are using ASE 12.0 or above you can do alter table drop column <column_name>. Otherwise do the following (and do not try alter table drop column as it will cause trouble and not supported!)

sp_rename "table", "table_prev"
Recreate the table excluding the column you do not want anymore. Make sure that all rules and defaults are added
Use the command below to copy the data from the original table to the new table. 
insert into table
(
 col1,
 col2,
 col3,
 ....
 coln
)
select
  col1,
  col2,
  col3,
  ....
  coln-1
from 
  table_prev

i.e. exclude the column that you do not want from the insert/select
(You may have to do the above in stages with begin and commit transaction to avoid filling up the transaction log!)
--Create the primary key (if existed before)
--Create the indexes
--Drop and create the triggers on the table
--Update statistics and do a sp_recompile on table
update statistics table
update index statistics table
sp_recompile table

Check that everything is OK in the new table. Drop table table_prev
good luck

 

[rchandr3]

Thanks Sybaseguru....

simple mistake leads to such a complex solving!