Microsoft为删除ALTER语句中的列部分指定的语法如下所示

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

注意[，…N]出现在列名之后和整个drop子句的末尾。这意味着有两种方法可以删除多个列。你可以这样做:

ALTER TABLE TableName
    DROP COLUMN Column1, Column2, Column3

或者这个

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

如果你想把删除列和删除约束结合起来，这第二个语法是有用的:

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;

对于MySQL(5.6版本)，你不能用一个drop-statement来执行多个drop-statement，而是使用多个drop-statement:

mysql> 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> 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> 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> 

顺便说一句，drop <col_name>是drop column <col_name>的缩写，正如您可以从上面的drop c3中看到的那样。

alter table tablename drop (column1, column2, column3......);

这可能有点晚了，但分享给新用户访问这个问题。 删除多列的实际语法是

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

因此，在Mysql 5.0.45中，你需要为每一列指定“drop column”。

对于SQL Server:

ALTER TABLE TableName
    DROP COLUMN Column1, Column2;

语法是

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

MySQL:

ALTER TABLE TableName
    DROP COLUMN Column1,
    DROP COLUMN Column2;

或者像这样:

ALTER TABLE TableName
    DROP Column1,
    DROP Column2;

COLUMN这个词是可选的，可以省略，除了RENAME COLUMN(用来区分列重命名操作和RENAME表重命名操作)。更多信息请点击这里。

总结

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;

请注意

DROP COLUMN不会物理删除某些DBMS的数据。例如，MS SQL。对于固定长度类型(int, numeric, float, datetime, uniqueidentifier等)，即使在删除列后添加的记录也会占用空间。为了消除浪费的空间做ALTER TABLE…重建。