How to Drop Multiple Columns with a Single Alter Table Statement in SQL Server

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

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 this¹:

ALTER TABLE TableName
    DROP Column1,
    DROP Column2;

¹ 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.

SQL Server - drop multiple columns with IF EXISTS at once

The syntax is a bit cumbersome but

ALTER TABLE t DROP COLUMN IF EXISTS col1, 
                   COLUMN IF EXISTS col2;

works fine

Drop Multiple Column Using single Alter

ALTER TABLE TableToDropColumn DROP COLUMN Field2, Field3

SQLFiddle demo

How to drop multiple columns in SQL Server

You don't need to write a loop to do this, one way is using sys.columns table to get all the columns which you want to drop.

Let say you want to drop all columns except 'Col11' and 'Col2' in that case you can write your query like following.

declare @dropstmt as nvarchar(max) ='alter table Test  drop column ' 
                          +  stuff((select ', ' + quotename(name) 
             from   
             (
                select c.name 
                from sys.columns c
                    JOIN sys.tables t ON c.object_id = t.object_id
                where t.name = 'test'
                and c.name  not in('col1','col2')
             )t
             for xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); 
print @dropstmt
exec sp_executesql @dropstmt  

Alter TABLE DROP Column - multiple columns MS SQL

You need dynamic query to drop the columns present in variable

exec('alter table TableName drop column '+@ColumnName)

Make sure the constraints are dropped before dropping the column

Modify multiple columns in single Alter Table query

Alter multiple columns in one time - impossible.

You could create Temp table with your edited columns, copy data from source table to temp table, drop your source table and rename temp table.

---

Related Topics