Switch to standard view 
  Sybase logo
 
 
 




Recent postings on Usenet state that SQL Server accepts the alter table drop column command. While this command may work in some cases, the command is still undocumented and unsupported, and may cause severe problems. Several internal bugs are listed for this command. Do not use it.


WARNING!
Using the alter table drop column command may cause recovery problems and database corruption (605 and 821 errors). This command is not supported by Sybase.

The alter table drop column functionality will be part of a future SQL standard and was not included in the System 10 or 11 release.

How to Drop a Column

The recommended way to drop a column from a table is as follows:

  1. Set the select into/bulkcopy option to "true", using sp_dboption.
  2. Use the select into command to copy the columns you want into a new table.
  3. select into new_table_name 
    from desired_column_list
    
  4. Drop the original table.
  5. Use sp_rename to give the new table the old name
  6. Drop and recreate all views, triggers, stored procedures, constraints, defaults, and so on, that referenced the dropped table.
  7. Set the select into/bulkcopy option to "false" with sp_dboption.
  8. Perform a database dump. See the SYBASE SQL Server System Administration Guide for instructions on performing a database dump following a minimally logged operation such as select into.


Note
You cannot dump the transaction log after performing a minimally logged operation. A database dump should be done immediately after a minimally logged operation to ensure uninterrupted backups.



Back to Top
© Copyright 2010, Sybase Inc.