CODEWITHSUNDEEP
sqlsql-servert-sql
Lukasz Szozda
Lukasz Szozda

Reputation: 175686

SQL Server - drop multiple columns with IF EXISTS at once

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?

Upvotes: 6

Views: 8149

Answers (1)

Martin Smith
Martin Smith

Reputation: 453212

The syntax is a bit cumbersome but 

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

works fine

Upvotes: 15

Related Questions