Using DROP TABLE IF EXISTS in SQL Server

Post Date:

January 26, 2024

,

In SQL Server, the DROP TABLE command is a straightforward way to delete a table. This blog post focuses on demonstrating the use of the DROP TABLE command with the IF EXISTS argument, a feature available in SQL Server 2016 and later.

The DROP TABLE IF EXISTS statement allows for the conditional removal of a table. If the specified table exists, it is dropped; otherwise, no error is generated, making it a handy tool for database management.

Drop Table if Exists SQL Server Example

Let’s walk through a practical example. First, we create a sample table using the CREATE TABLE command:

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

Now, we use the DROP TABLE IF EXISTS command to delete the table. Remarkably, even if the table is not present, no execution error occurs:

-- Drop table if exists SQL Server
DROP TABLE IF EXISTS demoTable;

Running the same command again won’t trigger any issues, if we do this as a regular DROP TABLE statement an error will show.

Msg 3701, Level 11, State 5, Line 13
Cannot drop the table 'demotable', because it does not exist or you do not have permission.

Verifying a Table Exists in SQL Server

An alternative approach involves checking the existence of a table using system tables. This query checks the sys.tables system table for the presence of a table named ‘demoTable’. If the table exists, it prints a message indicating its existence; otherwise, it prints a message indicating that the table does not exist. You can modify this query based on your specific needs.

-- Verifying a table exists SQL Server
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'demoTable')
BEGIN
    -- Table exists, you can perform actions accordingly
    PRINT 'Table demoTable exists';
END
ELSE
BEGIN
    -- Table does not exist, you can create the table or take other actions
    PRINT 'Table demoTable does not exist';
END