Drop Table if Exists in SQL Server

The DROP TABLE command in SQL Server removes a table from the database, as its name suggests.

In this post, we’ll look at how to use the DROP TABLE IF EXISTS command, which makes dropping tables more efficient and error-free by adding a conditional check.

Example: Drop Table if Exists

The DROP TABLE IF EXISTS command in SQL Server allows you to drop a table only if it already exists in the database. This prevents execution errors that occur when trying to drop a non-existent table.

This feature is supported in SQL Server 2016 and later versions.

In the example below, I demonstrate how this command works by first creating a new table, followed by running the DROP TABLE IF EXISTS command twice. The first execution successfully drops the table, while the second execution skips over the non-existent table without causing an error.

# create a test table ms sql
CREATE TABLE demoTable (r_id INT, r_name VARCHAR(100), r_description VARCHAR(200));

# drop the above table
DROP TABLE IF EXISTS T1;

# drop the table again, no execution errors / script will continue
DROP TABLE IF EXISTS T1;

SQL 2016 or Below

If you’re working with older SQL Server versions (pre-2016), we can get this information from system tables (sys.tables) to verify if a SQL Object exists or not before dropping.

Here’s an example for this method:

-- Check if the table exists before creating it  
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'demoTable')  
BEGIN  
    DROP TABLE demoTable;  
END