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

Question

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

User · Answer

create table test  a int  b int   c int  d int   alter table test drop column b  d    Be aware that DROP COLUMN does not physically remove the data  and for fixed length types  int  numeric  float  datetime  uniqueidentifier etc  the space is consumed even for records added after the columns were dropped  To get rid of the wasted space do ALTER TABLE     REBUILD

User · Answer

If it is just single column to delete the below syntax works  ALTER TABLE tablename DROP COLUMN column1   For deleting multiple columns  using the DROP COLUMN doesnot work  the below syntax works  ALTER TABLE tablename DROP  column1  column2  column3

User · Answer

Generic   ALTER TABLE table name  DROP COLUMN column1 column2 column3    E g    ALTER TABLE Student  DROP COLUMN Name  Number  City

User · Answer

for postgis is alter table table01 drop columns col1  drop col2

User · Answer

alter table tablename drop  column1  column2  column3

User · Answer

Select column to drop  ALTER TABLE my table DROP column to be deleted   However  some databases  including SQLite  have limited support  and you may have to create a new table and migrate the data over  https   www sqlite org faq html q11

User · Answer

Summarizing  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      Be aware  DROP COLUMN does not physically remove the data for some DBMS  E g  for MS SQL  For fixed length types  int  numeric  float  datetime  uniqueidentifier etc  the space is consumed even for records added after the columns were dropped  To get rid of the wasted space do ALTER TABLE     REBUILD

User · Answer

ALTER table table name Drop column column1  Drop column column2 Drop column column3    for MySQL DB   Or you can add some column while altering in the same line   ALTER table table name Drop column column1  ADD column column2 AFTER column7

User · Answer

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

User · Answer

The Syntax as specified by Microsoft for the dropping a column part of an ALTER statement is this   DROP            CONSTRAINT                      constraint name              WITH                lt drop clustered constraint option gt        n                                 n           COLUMN                    column name                n            n     Notice that the      n  appears after both the column name and at the end of the whole drop clause  What this means is that there are two ways to delete multiple columns  You can either do this   ALTER TABLE TableName     DROP COLUMN Column1  Column2  Column3   or this  ALTER TABLE TableName     DROP          COLUMN Column1          COLUMN Column2          COLUMN Column3   This second syntax is useful if you want to combine the drop of a column with dropping a constraint   ALTER TBALE TableName     DROP         CONSTRAINT DF TableName Column1          COLUMN Column1    When dropping columns SQL Sever does not reclaim the space taken up by the columns dropped  For data types that are stored inline in the rows  int for example  it may even take up space on the new rows added after the alter statement  To get around this you need to create a clustered index on the table or rebuild the clustered index if it already has one  Rebuilding the index can be done with a REBUILD command after modifying the table  But be warned this can be slow on very big tables  For example   ALTER TABLE Test     REBUILD

User · Answer

For MySQL  ver 5 6   you cannot do multiple column drop with one single drop-statement but rather multiple drop-statements   mysql gt  alter table test2 drop column  c1 c2 c3   ERROR 1064  42000   You have an error in your SQL syntax  check the manual that corresponds to your MySQL server version for the right syntax to use near   c1 c2 c3   at line 1 mysql gt  alter table test2 drop column c1 c2 c3  ERROR 1064  42000   You have an error in your SQL syntax  check the manual that corresponds to your MySQL server version for the right syntax to use near  c2 c3  at line 1 mysql gt  alter table test2 drop column c1  drop column c2  drop c3  Query OK  0 rows affected  0 64 sec  Records  0  Duplicates  0  Warnings  0  mysql gt     BTW  drop  lt col name gt  is shorthanded for drop column  lt col name gt  as you can see from drop c3 above

User · Answer

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        So for every column you need to specify  drop column  in Mysql 5 0 45

User · Answer

this query will alter the multiple column test it   create table test a int B int C int    alter table test drop a B

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

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to alter