How to DROP multiple columns with a single ALTER TABLE statement in SQL Server?

Jesse Webb picture Jesse Webb · Jun 14, 2011 · Viewed 406.7k times · Source

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

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)?

Answer

Alex Aza picture Alex Aza · Jun 14, 2011

For SQL Server:

ALTER TABLE TableName
    DROP COLUMN Column1, Column2;

The syntax is

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

For MySQL:

ALTER TABLE TableName
    DROP COLUMN Column1,
    DROP COLUMN Column2;

or like this1:

ALTER TABLE TableName
    DROP Column1,
    DROP Column2;

1 The word COLUMN is optional and can be omitted, except for RENAME COLUMN (to distinguish a column-renaming operation from the RENAME table-renaming operation). More info here.