SQL Book

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.

Syntax

The syntax can differ slightly depending on which database you are running.

SQL Server

DROP TABLE [IF EXISTS] TableName

DROP IF EXISTS is only available from SQL Server 2016 onwards.

MySQL

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;

Conclusion

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.

Join our newsletter

Subscribe to our free weekly newsletter and get exclusive SQL tips and articles straight to your inbox. 

 Your email address is only used to send you our newsletter.