DROP IF EXISTS

Leave a comment

March 23, 2020 by Kenneth Fisher

It’s amazing the things that SQL can do these days. It’s also amazing how many of them I’ve managed to completely miss over time. I was having a random conversation with a friend on Twitter the other day and they mentioned an acronym DIE. After asking for an explanation (and I’m really glad I did) they gave me this:

DROP DATABASE IF EXISTS DBName1, DBName2, DBName3;

After picking my jaw up off the floor I did a bit of looking. It turns out the DROP xxxx IF EXISTS name,…n is now the pattern for dropping stuff. For example:

DROP PROCEDURE IF EXISTS SPName1, SPName2, SPName3;
DROP VIEW IF EXISTS ViewName1, ViewName2, ViewName3;
DROP TABLE IF EXISTS DBName1.SchemaName.TblName1, SchemaName.TblName2, TblName3;

This is HUGE. It makes code so much more readable and less likely to error. For me it’s right up there with the CREATE OR ALTER pattern. No more DROP then CREATE or ALTER, no more IF EXISTS () DROP xxxx.

Both of these have been around since SQL 2016 so I really don’t have any good excuses for not already knowing them but at least now I do! (And so do you in case you’d missed it like I did.)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,116 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: