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: