Drop Multiple Column Using single Alter

Question

I searched a lot on web, but couldn't find the answer for dropping multiple columns in single alter statement using SQL Server 2008.

I tried following query

ALTER TABLE TableToDropColumn DROP (Field2, Field3)

but it throws an error

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.

juergen d · Accepted Answer · 2013-12-07 11:28:48Z

7

ALTER TABLE TableToDropColumn DROP COLUMN Field2, Field3

SQLFiddle demo

answered Dec 7, 2013 at 11:28

juergen d

205k40 gold badges303 silver badges376 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mihai · Accepted Answer · 2013-12-07 11:29:17Z

2

ALTER TABLE TableToDropColumn DROP Field2, DROP Field3

answered Dec 7, 2013 at 11:29

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

1 Comment

David Hladky Over a year ago

ALTER TABLE TableToDropColumn DROP COLUMN Field2, DROP COLUMN Field3.... is the only way, that works in Postgres.

Rahul Tripathi · Accepted Answer · 2013-12-07 11:29:23Z

2

ALTER TABLE TableToDropColumn DROP COLUMN Field2, Field3

On a side note:

You may also need to know how to get rid of the wasted space which is created after the above command.You can do ALTER TABLE ... REBUILD. Actually DROP COLUMN does not physically remove the data and the space is consumed even for records added after the columns were dropped. So thats why you need to use ALTER TABLE ... REBUILD to get rid of the wasted space

answered Dec 7, 2013 at 11:29

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

Collectives™ on Stack Overflow

Drop Multiple Column Using single Alter

3 Answers 3

SQLFiddle demo

Comments

1 Comment

Comments

Hot Network Questions