Question:
How do I delete a column, and all the values in it? Truncating will delete only the values, not the column itself. And I don’t want to drop the whole table.
Answer:
Thanks to Jose Muller for pointing out the right answer…
SQL Server 7 supports dropping a column using the alter table tablename drop column column_name. However, there are some limitations. You can’t drop a column with a primary key constraint or that is referenced by a foreign key. You also can’t drop the column if it is used in an index, check constraint, is bound to a rule, or is replicated.
Enterprise Manager also provides a way to automate the necessary steps of building a new table without the column, copying the data from the old table, dropping the old table, and then renaming the new table in cases where you can’t use the above syntax. (Note: EM uses the same approach even in cases where it could just drop the column.)
If you right-click on a table in Enterprise Manager and choose the Design menu option, you can make changes such as dropping a table. When you click the Save button, Enterprise Manager will generate the necessary SQL for all the above steps. This works fine for a development system. However, you should not run this directly in a production database.
Instead, examine the SQL it generates (you can save the SQL to a file) and evaluate whether your production system allows you to make such changes. You might have to schedule some downtime. Development or production, it would also be prudent to have a backup in case you accidentally point and click your way to the wrong change.
