SQL Server - drop multiple columns with IF EXISTS at once

Question

SQL Server supports syntax that allows to remove more than one column at a time. How to DROP multiple columns with a single ALTER TABLE statement in SQL Server?

Though it seems to be impossible to force it to work with IF EXISTS clasue for each occurence.

ALTER TABLE

DROP
| COLUMN [ IF EXISTS ]
      {
          column_name
      } [ ,...n ]

Conditionally drops the column or constraint only if it already exists.

Example:

CREATE TABLE t(i INT, col1 INT, col2 INT);

ALTER TABLE t DROP COLUMN IF EXISTS col1, col2;
-- col1, col2 were successfully removed

ALTER TABLE t DROP COLUMN IF EXISTS col1,  col2;
-- Msg 4924 Level 16 State 1 Line 1
-- ALTER TABLE DROP COLUMN failed because column 'col2' does not exist in table 't'.

Based on error message IF EXISTS takes effect only for first column.

db<>fiddle demo

Is there a limitation of this clause when combined with multiple columns at once?

Answer 1

The syntax is a bit cumbersome but

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

works fine