DROP IF EXISTS (DIE) pre-SQL Server 2016

SQL Server 2016 has a much more simple way to do this, but if you need to be able to re-run queries which create tables, and where the tables may or may not still exist, it can be difficult for a newbie to know how to do this gracefully.

If you run the queries and the table(s) already exist, your query errors out.

If you preface your query with a drop table query, and the table doesn’t exist, you also get stopped dead in your tracks.

SQL Server 2016 has a simple way to do this:

DROP TABLE IF EXISTS tablename;

My understanding is that this is not a new idea in the greater SQL world – it’s just newly implemented in SQL Server.

However, if you aren’t blessed with SQL Server 2016, there’s another way:

IF OBJECT_ID(‘[databasename].[schema].[tablename]’) IS NOT NULL DROP TABLE [databasename].[schema].[tablename];

What’s more, if you’re working with temp tables:

IF OBJECT_ID(‘tempdb..#temptablename’) IS NOT NULL DROP TABLE #tablename;

This is likely clunky for you smooth, practiced, experienced folks out there. But learning this was a god(dess?)-send for a newbie like myself. ­čÖé

Here’s a link from someone more practiced at these things than I:

DROP IF EXISTS