DROP IF EXISTS for columns

How To

Let’s start with the basic issue. I needed to drop a fixed set of columns should they exist in my table. Since this was on SQL Server 2016 the DROP IF EXISTS syntax sprang to mind. “This will work nicely!” I thought to myself. Yes. Yes it would. But first I had to track down the correct syntax.

First let’s set up a demo table:

Transact-SQL

1

2

3

4

5

6

7

DROPTABLEIFEXISTS#Test;

CREATETABLE#Test

(

col1int

,col2int

,col3int

);

I love DROP IF EXISTS. It makes things so much cleaner. You can see it being used above for the temp table.

It turns out this was heading down the right path as it would be a per-column option and the solution is just ahead, but first…

SQL Community

Fortunately for me the SQL Community is, without a doubt, one of the best technical communities out there and shortly after I posted a call for help on Twitter using the #sqlhelp hashtag, Robert Davis (b|t) replied back and we tested and came to the conclusion that it looked like it wouldn’t work for multiple columns. He followed up by sharing the question with the MVP list where Vassilis Papadimos replied back and provided the correct syntax which you will see in the solution below. I wasn’t able to locate a blog or a twitter account for Vassilis but would be happy to update in the future. This just goes to show the power and value of the #sqlhelp hashtag on twitter and, also, the SQL Server Community slack channels too.

The Solution

Transact-SQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

DROPTABLEIFEXISTS#Test;

CREATETABLE#Test

(

col1INT

,col2INT

,col3INT

);

SELECT*FROM#Test;

ALTERTABLE#Test

DROPCOLUMNIFEXISTScol2

,COLUMNIFEXISTScol3

,COLUMNIFEXISTScol4;

SELECT*FROM#Test;

In conclusion, I love the DROP IF EXISTS syntax even more now that I know how it works with columns and I also love being part of such an amazing SQL Community.