Jesse Webb Jesse Webb - 9 months ago 48
SQL Question

How do I DROP multiple columns with a single ALTER TABLE statement?

I would like to write a single SQL command to drop multiple columns from a single table in one


From MSDN's ALTER TABLE documentation...

DROP { [CONSTRAINT] constraint_name | COLUMN column_name }

Specifies that constraint_name or column_name is removed from the table. DROP COLUMN is not allowed if the compatibility level is 65 or earlier. Multiple columns and constraints can be listed.

It says that mutliple columns can be listed in the the statement but the syntax doesn't show an optional comma or anything that would even hint at the syntax.

How should I write my SQL to drop multiple columns in one statement (if possible)?

alter table TableName
    drop column Column1, Column2

The syntax is

DROP { [ CONSTRAINT ] constraint_name | COLUMN column } [ ,...n ]