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.
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.
The recommended way to drop a column from a table is as follows:
- Set the select into/bulkcopy option to "true", using sp_dboption.
- Use the select into command to copy the columns you want into a new table.
- Drop the original table.
- Use sp_rename to give the new table the old name
- Drop and recreate all views, triggers, stored procedures, constraints, defaults, and so on, that referenced the dropped table.
- Set the select into/bulkcopy option to "false" with sp_dboption.
- 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.
select into new_table_name from desired_column_list
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.