In SQL Server version 2016, Microsoft introduced an extremely handy tool called DROP IF EXISTS to make our database development lives so must easier.
In this very brief tutorial, we’ll walk through the syntax of the DROP IF EXISTS tool and show you a few examples of how to use it. We’ll discuss these topics:
- The syntax for DROP IF EXISTS
- Example of using DROP IF EXISTS to drop a table
- How to drop an object pre – SQL Server 2016
- Links
Let’s get into it:
1. The syntax for DROP IF EXISTS
It’s extremely simple:
DROP <object-type> IF EXISTS <object-name>
The object-type can be many different things, including:
Like you’d expect, SQL Server will drop the object if it exists. If the object doesn’t exist, nothing happens. The great thing about this feature is that you don’t get an error message if the object doesn’t exist in your database. We obviously want to do our best to reduce the chances of an error message popping up while our code is executing.
2. Example of using DROP IF EXISTS to drop a table
Let’s say we want to drop a table called “Spaghetti“. If we look in our Object Explorer, we see there isn’t a table named Spaghetti:
To be doubly sure, let’s look in the system table sys.tables:
Ok, so we’re confident this table doesn’t exist. Let’s run a DROP IF EXISTS statement against this table:
We get a nice message saying Commands completed successfully. The command didn’t really do anything, and that’s ok. This is much better than receiving an error message, which we would have received if we used the regular DROP TABLE statement:
Of course, understand that if the table did exist, it would have been dropped.
One place the DROP IF EXISTS syntax can be especially useful is when you’re working with temporary tables. Temp tables are very useful for gathering data in a non-permanent type of way. I usually see temp tables in the definition of stored procedures, for example. The scope of temp tables can be a bit tricky, so you may not be sure if the temp table exists when you attempt to drop it. You could use the DROP IF EXISTS statement to safely attempt to drop the temp table and be confident you won’t receive an error message in the event that the temp table has already been dropped.
3. How to drop an object pre – SQL Server 2016
Unfortunately, the DROP IF EXISTS statement hasn’t always been around. It was introduced in SQL Server 2016, which means it simply won’t work in any version older than that. If we want to safely attempt to drop an object in an older version, what should we do?
We can do things the hard way. The following syntax would be good for dropping a table:
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Spaghetti' AND type_desc = 'USER_TABLE') BEGIN DROP TABLE Spaghetti END
We can basically use the IF EXISTS decision structure to check if something is returned from a query against the sys.tables system table where we look for the table name we are attempting to drop. If there is a reference to that table, then it get’s dropped. Otherwise, if nothing is returned by the query (which means that table doesn’t exist in our database), then we skip the DROP TABLE statement and nothing happens.
Of course, the system table you query will depend on the object you are trying to drop. If you are attempting to drop a stored procedure, for example, you’ll likely want to query sys.procedures.
Either way, make sure your inner query is properly filtered. You want to make sure you are looking in the correct system table and isolating the one row that represents the object you are attempting to drop.
4. Links
You should consider getting the book T-SQL Fundamentals by Itzik Ben-Gan. It goes over several core concepts you should know about Microsoft SQL Server, including a discussion on the SQL Server DROP IF EXISTS statement. This book helped me out tremendously when I was first starting out with SQL Server, and I reference it all the time now. You won’t regret owning this book, trust me. Get it today!
Next Steps:
Leave a comment if you found this tutorial helpful!
Do you know how to drop a column from a table? Click the link to find out!
Also, click the link below to learn more about the IF EXIST decision structure:
SQL IF EXISTS Decision Structure: Explained with Examples
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, please leave a comment. Or better yet, send me an email!
