DROP IF EXISTS for columns

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:

DROP TABLE IF EXISTS #Test;
CREATE TABLE #Test 
	(
			col1 int
		,	col2 int
		,	col3 int
	);

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:

ALTER TABLE #Test
DROP COLUMN IF EXISTS col2, col3

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

ALTER TABLE #Test
DROP COLUMN IF EXISTS col2, IF EXISTS col3

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

DROP TABLE IF EXISTS #Test;
CREATE TABLE #Test 
	(
			col1 INT
		,	col2 INT
		,	col3 INT
	);

SELECT * FROM #Test;

ALTER TABLE #Test
DROP COLUMN IF EXISTS col2
	, COLUMN IF EXISTS col3
	, COLUMN IF EXISTS col4;

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.

Categorised in: Blog, Blogs