DROP IF EXISTS for columns

By on January 21, 2018

This post is half “how to” and half community bragging.

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:

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

I tried a couple of variations:

It ran! Then I ran it a second time. [Sad Trombone]

Incorrect syntax near the keyword ‘IF’. [mumbling expletives into aforementioned trombone]

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

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.

Categorised in:

Subscribe

Want occasional notes from me?
(I promise, no spam.)

* indicates required

© Copyright 2018 The Data You Need. All rights reserved.