Coder Perfect

In SQL Server, how can you DROP many columns with a single ALTER TABLE statement?

Problem

I’d like to create a single SQL command that allows me to drop many columns from a single table using a single ALTER TABLE declaration.

ALTER TABLE documentation from MSDN…

Multiple columns can be included in the statement, however the grammar does not provide an optional comma or anything else that would even hint at the syntax.

If possible, how should I design my SQL to drop numerous columns in one statement?

Asked by Jesse Webb

Solution #1

ALTER TABLE TableName
    DROP COLUMN Column1, Column2;

The syntax is

DROP { [ CONSTRAINT ] constraint_name | COLUMN column } [ ,...n ] 
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.

Answered by Alex Aza

Solution #2

Oracle:

ALTER TABLE table_name DROP (column_name1, column_name2);

MS SQL Server:

ALTER TABLE table_name DROP COLUMN column_name1, column_name2

MySQL:

ALTER TABLE table_name DROP column_name1, DROP column_name2;

PostgreSQL

ALTER TABLE table_name DROP COLUMN column_name1, DROP COLUMN column_name2;

For some DBMS, DROP COLUMN does not physically erase the data. For instance, in MS SQL. Even for entries added after the columns were dropped, space is required for fixed length types (int, numeric, float, datetime, uniqueidentifier, and so on). ALTER TABLE… REBUILD to get rid of the unused space.

Answered by Denis Rozhnev

Solution #3

create table test (a int, b int , c int, d int);
alter table test drop column b, d;

Be warned that DROP COLUMN does not erase the data physically, and for fixed length types (int, numeric, float, datetime, uniqueidentifier, and so on), the space is utilized even if records are added after the columns have been dropped. ALTER TABLE… REBUILD to get rid of the unused space.

Answered by Remus Rusanu

Solution #4

This may be late, but sharing it for the new users visiting this question. To drop multiple columns actual syntax is

alter table tablename drop column col1, drop column col2 , drop column col3 ....

In Mysql 5.0.45, you must specify “drop column” for each column.

Answered by MANISH ZOPE

Solution #5

The following is the Microsoft syntax for dropping a column as part of an ALTER statement.

 DROP 
 {
     [ CONSTRAINT ] 
     { 
          constraint_name 
          [ WITH 
           ( <drop_clustered_constraint_option> [ ,...n ] ) 
          ] 
      } [ ,...n ]
      | COLUMN 
      {
          column_name 
      } [ ,...n ]
 } [ ,...n ]

The [,…n] appears after the column name as well as at the end of the entire drop clause. This means there are two options for deleting several columns. You can do one of two things:

ALTER TABLE TableName
    DROP COLUMN Column1, Column2, Column3

or this

ALTER TABLE TableName
    DROP 
        COLUMN Column1,
        COLUMN Column2,
        COLUMN Column3

If you want to drop a column and a constraint at the same time, use the second syntax:

ALTER TBALE TableName
    DROP
        CONSTRAINT DF_TableName_Column1,
        COLUMN Column1;

When columns are dropped, SQL Sever does not recoup the space that the columns took up. It may even take up space on the additional rows added after the change statement for data types that are stored inline in the rows (int, for example). To go around this, you’ll need to establish a clustered index on the table, or rebuild an existing clustered index. After making changes to the table, use the REBUILD command to rebuild the index. However, on particularly large tables, this can take a long time. Consider the following scenario:

ALTER TABLE Test
    REBUILD;

Answered by Martin Brown

Post is based on https://stackoverflow.com/questions/6346120/how-to-drop-multiple-columns-with-a-single-alter-table-statement-in-sql-server