Drop Table if Exists in SQL Server

The DROP TABLE command in SQL Server is used to remove a table from the database permanently. However, attempting to drop a non-existent table can result in execution errors. To prevent this, SQL Server 2016 introduced the DROP TABLE IF EXISTS command, which makes the process more efficient and error-free.

In this post, we’ll explore how to use DROP TABLE IF EXISTS in SQL Server and discuss alternative methods for older versions.

What is DROP TABLE IF EXISTS?

The DROP TABLE IF EXISTS command allows you to delete a table only if it exists in the database. It has been support since SQL Server 2016 (13.x), and helps prevent script execution errors and ensures smoother database management.

Example: Using Drop Table if Exists

Below is an example demonstrating the use of DROP TABLE IF EXISTS. The script first creates a table, then drops it using this command, and attempts to drop it again without errors:

-- Create a test table in SQL Server
CREATE TABLE demoTable (
    r_id INT,
    r_name VARCHAR(100),
    r_description VARCHAR(200)
);

-- Drop the table if it exists
DROP TABLE IF EXISTS demoTable;

-- Drop the table again (no error will occur)
DROP TABLE IF EXISTS demoTable;

In this example:
1. We create a table named demoTable.
2. We execute DROP TABLE IF EXISTS demoTable to remove it.
3. Running the command again does not result in an error, since the table no longer exists.

Using DROP TABLE IF EXISTS in SQL Server 2014 or Below

If you are working with SQL Server 2014 or earlier, the DROP TABLE IF EXISTS command is not available. Instead, you need to check for the table’s existence using system tables before dropping it:

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

This method ensures compatibility with older SQL Server versions while still preventing errors when dropping non-existent tables.

Hope this was a useful guide for you!