The DROP TABLE IF EXISTS SQL statement explained with examples and tips
The DROP TABLE SQL statement enables you to delete a table from the database. If the table did not exist then attempting to DROP it would cause an error to occur.
The DROP TABLE IF EXISTS SQL statement enables a check to see that the table exists prior to attempting the dropping (deletion) of the table. If the table does not exists then the DROP TABLE statement is not executed so no error occurs.
The syntax can differ slightly depending on which database you are running.
DROP TABLE [IF EXISTS] TableName
DROP IF EXISTS is only available from SQL Server 2016 onwards.
DROP [TEMPORARY] TABLE [IF EXISTS] TableName
The TEMPORARY keyword can be used in MySQL to specify that only a temporary table can be deleted.
Examples of using DROP TABLE IF EXISTS
Example 1 - Deleting a table using DROP TABLE with the IF EXISTS clause
-- create a table CREATE TABLE dbo.Country ( Id int IDENTITY(1,1), Country varchar(50) ); -- create some rows in the table INSERT INTO Country (Country) VALUES ('Greenland'), ('Iceland'), ('Norway'); -- DROP the table if it exists IF OBJECT_ID('dbo.Country', 'u') IS NOT NULL DROP TABLE dbo.Country;
Example 2 - Error that occurs when using DROP TABLE without the IF EXISTS clause
-- if we attempt to DROP the table that we just created and dropped then we will get an error DROP TABLE dbo.Country;
The error returned is: Cannot drop the table 'dbo.Country', because it does not exist or you do not have permission.
Example 3 - Using with temp tables in SQL Server
-- create a temporary table CREATE TABLE dbo.#Continent ( Id int IDENTITY(1,1), Continent varchar(50) ); INSERT INTO dbo.#Continent (Continent) VALUES ('Africa'); SELECT * FROM dbo.#Continent; DROP TABLE IF EXISTS dbo.#Continent;
Example 4 - Using with temporary tables in MySQL
-- temp table MySQL CREATE TEMPORARY TABLE Continent( Id INT PRIMARY KEY, Continent varchar(50) ); DROP TEMPORARY TABLE IF EXISTS Continent;
Alternative methods for dropping a table with an exists check
You can use the INFORMATION_SCHEMA of the database to perform the same functionality. This method is supported by most of the majore databases including SQL Server, MySQL, Oracle, PostGres, IBM DB2. The INFORMATION_SCHEMA is ANSI SQL compliant and is intended to enable the finding of database object information.
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'Country' AND TABLE_SCHEMA = 'dbo') DROP TABLE dbo.Country;
If you are running a version of SQL Server prior to SQL Server 2016 then you can use the following method to achieve the same purpose as DROP TABLE IF EXISTS. This involves using an IF statement in combination with the OBJECT_ID function. The 2nd parameter of the OBJECT_ID function is passed a 'u'. This represents the type of object to check for and in this case 'u' stands for a 'User-defined table'.
-- method 1 with OBJECT_ID function IF OBJECT_ID('dbo.Country', 'u') IS NOT NULL DROP TABLE dbo.Country; -- method 2 by querying sys.objects if EXISTS (SELECT * from sys.objects WHERE name = 'Country' and type = 'u') DROP TABLE Country;
We have seen in this article how using the IF EXISTS clause with the DROP TABLE statement provides a simple one-line method of checking whether a table exists before attempting its deletion. The DROP IF EXISTS method can also be used with other types of database objects to enable a consistent, easy approach to writing data definition language (DDL) statements in your SQL code.
If you are running a database platform that does not support DROP IF EXISTS then we have explored a couple of alternative methods you can use to achieve the same results albeit in a less concise way.