SQL Server DROP TABLE IF EXISTS Examples

Problem

You may be deploying a table to a SQL database and need to be sure a table with the same name doesn’t still exist or attempting do a CREATE TABLE will fail. If you try to do a DROP TABLE first and the table does not exist, it will also fail with the error “Msg 3701 Level 11 State 5, 25 Cannot drop the table because it does not exist or you do not have permission”. How do you solve these problems with SQL DROP Table If Exists?

Solution

The solution is to add conditional logic to your T-SQL to check if the specified table exists before trying to drop the table. If it exists, you drop the table, if it doesn’t exist you can skip the DROP TABLE.

In this tutorial, we’ll look at an example of what we see if we attempt to drop a table that doesn’t exist and the following ways to conditionally run DROP TABLE:

OBJECT_ID() function (all supported versions)
Querying the sys.tables System View (all supported versions)
Querying the INFORMATION_SCHEMA.TABLES View (all supported versions)
DROP TABLE with IF EXISTS (SQL Server 2016 and up)

DROP TABLE Command Permissions Required

The DROP TABLE statement requires the user to have one of the following:

ALTER permission on the table’s schema
CONTROL permission on the table
Membership in the db_ddladmin fixed database role

Setup

To get started, I’ve created a new database called MyDatabase for my examples.

Drop Table that Does Not Exist

First, we’ll try to drop a table that does not exist without using any conditional logic.

-- use database
USE [MyDatabase];
GO
-- run DROP TABLE without conditional logic
DROP TABLE [dbo].[MyTable0];
GO

The DROP TABLE will run and fails with message ‘Msg 3701, Level 11, State 5, Line 5 Cannot drop the table ‘dbo.MyTable0’, because it does not exist or you do not have permission.’ simply because it’s trying to drop an object that isn’t there.

Option 1 – DROP TABLE if exists using OBJECT_ID() function (all supported versions)

Using OBJECT_ID() will return an object id if the name and type passed to it exists.

In this example we pass the name of the table and the type of object (U = user table) to the function and a NULL is returned where there is no record of the table and the DROP TABLE is ignored.

-- use database
USE [MyDatabase];
GO
-- pass table name and object type to OBJECT_ID - a NULL is returned if there is no object id and DROP TABLE is ignored 
IF OBJECT_ID(N'dbo.MyTable0', N'U') IS NOT NULL  
   DROP TABLE [dbo].[MyTable0];  
GO

Option 2 – DROP TABLE if exists querying the sys.tables System View (all supported versions)

Another way to see if a table exists is by querying the sys.tables system view to see if there is an entry for the table and schema names.

-- use database
USE [MyDatabase];
GO
-- check to see if table exists in sys.tables - ignore DROP TABLE if it does not
IF EXISTS(SELECT * FROM sys.tables WHERE SCHEMA_NAME(schema_id) LIKE 'dbo' AND name like 'MyTable0')  
   DROP TABLE [dbo].[MyTable0];  
GO

DROP TABLE will not run because there is no row returned from sys.systables in the EXISTS clause.

Option 3 – DROP TABLE if exists querying the INFORMATION_SCHEMA.TABLES View (all supported versions)

We can also query the ISO compliant INFORMATION_SCHEMA.TABLES view to see if the table exists.

-- use database
USE [MyDatabase];
GO
-- check to see if table exists in INFORMATION_SCHEMA.TABLES - ignore DROP TABLE if it does not
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'MyTable0' AND TABLE_SCHEMA = 'dbo')
   DROP TABLE [dbo].[MyTable0];
GO

DROP TABLE is ignored because there is no record returned.

Option 4 – DROP TABLE IF EXISTS (SQL Server 2016 and up)

The previous T-SQL examples will work in all supported versions. However, SQL Server 2016 introduced a simpler way of dropping a table with DROP IF EXISTS.

Instead of having to look and see if whether or not the table exists with one T-SQL statement then running DROP TABLE if it does, and ignored if not, executing DROP TABLE IF EXISTS will do both for you in one line.

-- use database
USE [MyDatabase];
GO
-- attempt to run DROP TABLE only if it exists 
DROP TABLE IF EXISTS [dbo].[MyTable0];
GO

DROP TABLE does not attempt to drop the non-existent table.

Comparison of DROP TABLE if exists options

To demonstrate what it looks like when the table does exist, I’ll create four simple tables named MyTable1 through MyTable4 and insert a record in each.

-- use database
USE [MyDatabase];
GO
-- create and populate MyTable1
CREATE TABLE [dbo].[MyTable1]([Col1] [int] NULL);
GO
INSERT INTO [dbo].[MyTable1](Col1) VALUES(1);
GO
-- create and populate MyTable2
CREATE TABLE [dbo].[MyTable2] ([Col1] [int] NULL);
GO
INSERT INTO [dbo].[MyTable2](Col1) VALUES(1);
GO
-- create and populate MyTable3
CREATE TABLE [dbo].[MyTable3]([Col1] [int] NULL);
GO
INSERT INTO [dbo].[MyTable3](Col1) VALUES(1);
GO
-- create and populate MyTable4
CREATE TABLE [dbo].[MyTable4]([Col1] [int] NULL);
GO
INSERT INTO [dbo].[MyTable4](Col1) VALUES(1);
GO

Now, we’ll run DROP TABLE unconditionally then run it conditionally based on whether anything is returned from OBJECT_ID(), sys.tables, and INFORMATION_SCHEMA.TABLES to demonstrate what it looks like when we run the above T-SQL against existing tables.

-- use database
USE [MyDatabase];
GO
-- run DROP TABLE unconditionally
DROP TABLE [dbo].[MyTable1];
GO
-- run DROP TABLE if OBJECT_ID() returns a row 
IF OBJECT_ID('dbo.MyTable2', 'u') IS NOT NULL 
   DROP TABLE [dbo].[MyTable2];  
GO
-- run DROP TABLE if there is a row in sys.tables
IF EXISTS(SELECT * FROM sys.tables WHERE SCHEMA_NAME(schema_id) LIKE 'dbo' AND name like 'MyTable3')  
   DROP TABLE [dbo].[MyTable3];  
GO
-- run DROP TABLE if there is a row in INFORMATION_SCHEMA.TABLES
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'MyTable4' AND TABLE_SCHEMA = 'dbo')
   DROP TABLE [dbo].[MyTable4];
GO

Each statement was successful and DROP TABLE ran because the database object was found for each statement. With this code, we have a dropped table for each executed command.

Drop Table when Referential Integrity is in place

Dropping tables becomes slightly more complex when there are foreign key relationships involved.

We’ll create three simple tables called customers, orders (dependent on customers), and order_items (dependent on orders) with the following DDL commands.

-- use database
USE [MyDatabase]
GO
 
-- create customers table
CREATE TABLE [dbo].[customers]
(
    [customer_id] [int] IDENTITY(1, 1) NOT NULL
  , [first_name] [varchar](255) NOT NULL
  , [last_name] [varchar](255) NOT NULL
  , [phone] [varchar](25) NULL
  , [email] [varchar](255) NOT NULL
  , [street] [varchar](255) NULL
  , [city] [varchar](50) NULL
  , [state] [varchar](25) NULL
  , [zip_code] [varchar](5) NULL
  ,
  PRIMARY KEY CLUSTERED ([customer_id] ASC)
);
GO
 
-- populate customers table
INSERT INTO [dbo].[customers] ([first_name],[last_name],[phone],[email],[street],[city],[state],[zip_code])
VALUES ('John', 'Doe', '123-456-7890', 'john@doe.com', '100 Main St', 'AnyTown', 'MA', '12345'),
       ('Jane', 'Doe', '123-456-7890', 'jane@doe.com', '100 Main St', 'AnyTown', 'MA', '12345'),
       ('Bob', 'Smith', '123-456-8901','bob@smith.com', '100 Elm St', 'AnyTown', 'MA', '12345');
GO
 
 
-- create orders table
CREATE TABLE [dbo].[orders]
(
    [order_id] [int] IDENTITY(1, 1) NOT NULL
  , [customer_id] [int] NULL
PRIMARY KEY CLUSTERED ([order_id] ASC)
);
 
-- add foreign key referencing [dbo].[customers] table
ALTER TABLE [dbo].[orders]
ADD
FOREIGN KEY ([customer_id]) REFERENCES [dbo].[customers] ([customer_id]);
GO
 
-- populate orders table
INSERT INTO [dbo].[orders] ([customer_id])
VALUES (3),
       (2);
GO
 
-- create order_items table
CREATE TABLE [dbo].[order_items]
(
    [order_id] [int] NOT NULL
  , [item_id] [int] NOT NULL
  , [product_id] [int] NOT NULL
  , [quantity] [int] NOT NULL
  , [list_price] [decimal](10, 2) NOT NULL
  , [discount] [decimal](4, 2) NOT NULL
  ,
  PRIMARY KEY CLUSTERED
  (
      [order_id] ASC
    , [item_id] ASC
  )
);
GO
 
-- add foreign key referencing [dbo].[orders] table
ALTER TABLE [dbo].[order_items]
ADD
FOREIGN KEY ([order_id]) REFERENCES [dbo].[orders] ([order_id]);
GO
 
-- populate order_items tables
INSERT INTO [dbo].[order_items]
VALUES (1,10,100,25,999.99,0),
       (2,11,  5,99,800.00,0);
GO

Here we see a graphical representation of the tables just created.

Let’s see what happens when we attempt to drop the three tables in this order.

DROP TABLE IF EXISTS [dbo].[customers];   
DROP TABLE IF EXISTS [dbo].[orders];      
DROP TABLE IF EXISTS [dbo].[order_items];
GO

We get a ‘Msg 3726, Level 16, State 1, Line 10 Could not drop object ‘schema.table’ because it is referenced by a FOREIGN KEY constraint.’ Error on [dbo].[customers] and [dbo].[orders] tables but [dbo].[order_items] is dropped. This is because [dbo].[order_items] is the only table without a dependency.

How can we drop all three? First we’ll recreate and populate [dbo].[order_items].

-- create order_items table
CREATE TABLE [dbo].[order_items]
(
    [order_id] [int] NOT NULL
  , [item_id] [int] NOT NULL
  , [product_id] [int] NOT NULL
  , [quantity] [int] NOT NULL
  , [list_price] [decimal](10, 2) NOT NULL
  , [discount] [decimal](4, 2) NOT NULL
  ,
  PRIMARY KEY CLUSTERED
  (
      [order_id] ASC
    , [item_id] ASC
  )
);
GO
 
-- add foreign key referencing [dbo].[orders] table
ALTER TABLE [dbo].[order_items]
ADD
FOREIGN KEY ([order_id]) REFERENCES [dbo].[orders] ([order_id]);
GO
 
-- populate order_items tables
INSERT INTO [dbo].[order_items]
VALUES (1,10,100,25,999.99,0),
       (2,11,  5,99,800.00,0);
GO

And now simply execute the statements in the order of dependencies so each dependency is removed before the next table is dropped.

DROP TABLE IF EXISTS [dbo].[order_items]; -- removes dependency on [dbo].[orders]
DROP TABLE IF EXISTS [dbo].[orders];      -- removes dependency on [dbo].[customers]
DROP TABLE IF EXISTS [dbo].[customers];   
GO

Alternatively, we can query sys.foreign_keys, filtering on the tables to drop for the foreign key names.

SELECT OBJECT_SCHEMA_NAME(parent_object_id) AS [Schema]
     , OBJECT_NAME(parent_object_id)        AS [Table]
     , name                                 AS [Name]
FROM sys.foreign_keys
WHERE OBJECT_NAME(parent_object_id) IN ('customers','orders','order_items'); -- tables we want to drop
GO

Then run an ALTER TABLE [schema].[table] DROP CONSTRIANT to drop the constraints. Without the constraints we can drop the tables in any order we like.

ALTER TABLE [dbo].[orders]      DROP CONSTRAINT FK__orders__customer__1209AD79;
ALTER TABLE [dbo].[order_items] DROP CONSTRAINT FK__order_ite__order__14E61A24;GO
 
DROP TABLE IF EXISTS [dbo].[customers];   
DROP TABLE IF EXISTS [dbo].[orders];      
DROP TABLE IF EXISTS [dbo].[order_items];
GO

Drop Table When Schema Binding is in Place

Schema Binding is a method to bind a view, function, or stored procedure to an underlying table. It prohibits a change to or drop of a table that would affect the bound object. It requires the bound object(s) to be changed before the table change or deletion can happen.

To demonstrate, we’ll create and populate a table called employees and create a view over that selects three columns from it in SSMS.

-- create employees
CREATE TABLE [dbo].[employees] (
[LastName] [varchar](100) NOT NULL,
[FirstName] [varchar](100) NOT NULL,
[Title] [varchar](50) NULL,
[HireDate] [date] NULL,
[TerminationDate] [date] NULL,
[RehireDate] [date] NULL,
[Address] [varchar](100) NULL,
[City] [varchar](100) NULL,
[Region] [varchar](100) NULL,
[PostalCode] [varchar](100) NULL);
GO
 
-- populate employees
INSERT INTO [dbo].[Employees]
VALUES('Jones', 'Robert', 'Accountant', '2010-07-01', NULL, NULL, '1 Main St', 'Anywhere', 'MA', '12345'), 
      ('Forth', 'Sally', 'Director', '2010-07-15', NULL, NULL, '27 Elm St', 'Somewhere', 'NH', '23456');
GO
 
-- create view vEmployees
CREATE VIEW [dbo].[vEmployees] AS(
SELECT [FirstName],[LastName],[Title]
FROM [dbo].[Employees]
);
GO

And here we query the view.

SELECT [FirstName], [LastName], [Title]
FROM [dbo].[vEmployees];
GO

Let’s drop one of the columns in the table.

ALTER TABLE [dbo].[employees] DROP COLUMN [Title]
GO

The same query of the view now returns an error because an underlying column is missing and now the view is broken.

SELECT [FirstName], [LastName], [Title]
FROM [dbo].[vEmployees];
GO

How can we prevent this from happening? To demonstrate let’s drop, recreate, and populate the table.

-- drop and recreate employees
DROP TABLE [dbo].[employees];
GO
 
CREATE TABLE [dbo].[employees] (
[LastName] [varchar](100) NOT NULL,
[FirstName] [varchar](100) NOT NULL,
[Title] [varchar](50) NULL,
[HireDate] [date] NULL,
[TerminationDate] [date] NULL,
[RehireDate] [date] NULL,
[Address] [varchar](100) NULL,
[City] [varchar](100) NULL,
[Region] [varchar](100) NULL,
[PostalCode] [varchar](100) NULL);
GO
 
-- populate employees
INSERT INTO [dbo].[Employees]
VALUES('Jones', 'Robert', 'Accountant', '2010-07-01', NULL, NULL, '1 Main St', 'Anywhere', 'MA', '12345'), 
      ('Forth', 'Sally', 'Director', '2010-07-15', NULL, NULL, '27 Elm St', 'Somewhere', 'NH', '23456');
GO

And this time we’ll drop and recreate the view adding WITH SCHEMABINDING.

-- drop and recreate view vEmployees with schemabinding this time
DROP VIEW [dbo].[vEmployees];
GO
 
CREATE VIEW [dbo].[vEmployees] WITH SCHEMABINDING AS(
SELECT [FirstName],[LastName],[Title]
FROM [dbo].[Employees]
);
GO

Now, attempt to alter a column used in the bound view or to drop the table will fail.

-- attempt to drop column
ALTER TABLE [dbo].[customers] DROP COLUMN [last_name];
GO
 
-- attempt to drop table
DROP TABLE [dbo].[customers];
GO

Other Things to be Aware of When You Drop a Table

Some other functionality to be aware of when a table is dropped are:

Views
Stored Procedures
Functions
Triggers
Indexes
Statistics

If a table is dropped and there are associated views, stored procedures or functions that were created without schema binding, then stored procedures, functions, and views will still exist but will no longer work. But, what about triggers, indexes, and statistics?

To demonstrate, we’ll:

Create and populate the employees table again
Create an index, function and trigger on it
Create a stored procedure, function, and view that query it

USE MyDatabase;
GO
 
-- create table
CREATE TABLE [dbo].[employees] (
[LastName] [varchar](100) NOT NULL,
[FirstName] [varchar](100) NOT NULL,
[Title] [varchar](50) NULL,
[HireDate] [date] NULL,
[TerminationDate] [date] NULL,
[RehireDate] [date] NULL,
[Address] [varchar](100) NULL,
[City] [varchar](100) NULL,
[Region] [varchar](100) NULL,
[PostalCode] [varchar](100) NULL);
GO
 
-- populate table
INSERT INTO [dbo].[Employees]
VALUES('Jones', 'Robert', 'Accountant', '2010-07-01', NULL, NULL, '1 Main St', 'Anywhere', 'MA', '12345'), 
      ('Forth', 'Sally', 'Director', '2010-07-15', NULL, NULL, '27 Elm St', 'Somewhere', 'NH', '23456');
GO
 
-- create index
CREATE CLUSTERED INDEX [idx_Title] ON [dbo].[employees]
(
   [Title] 
);
GO
 
-- create statistic
CREATE STATISTICS [MyStat] ON [dbo].[employees]([LastName])
GO
 
-- create trigger
CREATE TRIGGER [dbo].tr_employees 
   ON  [dbo].[employees]
   AFTER INSERT
AS 
BEGIN
   SELECT * FROM inserted
END
GO
 
-- create view  
CREATE VIEW [dbo].[vEmployees] AS(
SELECT [FirstName],[LastName],[Title]
FROM [dbo].[Employees]
);
GO
 
-- create procedure
CREATE PROCEDURE [dbo].[uspNumOfEmp] AS
BEGIN
   SELECT COUNT(*) FROM [dbo].[employees]
END;
GO
 
-- create function
CREATE FUNCTION dbo.NumOfEmp()
RETURNS INT
AS 
BEGIN
   RETURN (SELECT COUNT(*) FROM [dbo].[employees])
END
GO

Here, querying the related system views, we see our view, stored procedure, and function.

SELECT name AS [View]      FROM sys.views      WHERE name LIKE 'vemployees'
SELECT name AS [Procedure] FROM sys.procedures WHERE name LIKE 'uspNumOfEmp'
SELECT name AS [Function]  FROM sys.objects    WHERE name LIKE 'NumOfEmp'

And here, we see our trigger, index, and statistic.

SELECT name AS [Trigger]   FROM sys.triggers   WHERE name LIKE 'tr_employees'
SELECT name AS [Index]     FROM sys.indexes    WHERE name LIKE 'idx_Title'
SELECT name AS [Statistic] FROM sys.stats      WHERE name LIKE 'MyStat'

Now, let’s drop the table, then see how attempting to query the table, view, or function behaves.

DROP TABLE [dbo].[employees];
GO
 
SELECT * FROM [dbo].[employees]; 
SELECT * FROM [dbo].[vEmployees]; 
EXECUTE [dbo].[uspNumOfEmp]; 
SELECT dbo.NumOfEmp();
GO

We see they all fail.

Recreate and populate the table again.

-- create table
CREATE TABLE [dbo].[employees] (
[LastName] [varchar](100) NOT NULL,
[FirstName] [varchar](100) NOT NULL,
[Title] [varchar](50) NULL,
[HireDate] [date] NULL,
[TerminationDate] [date] NULL,
[RehireDate] [date] NULL,
[Address] [varchar](100) NULL,
[City] [varchar](100) NULL,
[Region] [varchar](100) NULL,
[PostalCode] [varchar](100) NULL);
GO
 
-- populate table
INSERT INTO [dbo].[Employees]
VALUES('Jones', 'Robert', 'Accountant', '2010-07-01', NULL, NULL, '1 Main St', 'Anywhere', 'MA', '12345'), 
      ('Forth', 'Sally', 'Director', '2010-07-15', NULL, NULL, '27 Elm St', 'Somewhere', 'NH', '23456');
GO
SELECT * FROM [dbo].[employees]; 
SELECT * FROM [dbo].[vEmployees]; 
EXECUTE [dbo].[uspNumOfEmp]; 
SELECT dbo.NumOfEmp();
GO

And querying the table, view, or function behaves normally again.

But here we’ll see the trigger, index, and statistic are no longer there and have to be recreated.

SELECT name AS [Trigger]   FROM sys.triggers   WHERE name LIKE 'tr_employees'
SELECT name AS [Index]     FROM sys.indexes    WHERE name LIKE 'idx_Title'
SELECT name AS [Statistic] FROM sys.stats      WHERE name LIKE 'MyStat'

-- create trigger
CREATE TRIGGER [dbo].tr_employees 
   ON  [dbo].[employees]
   AFTER INSERT
AS 
BEGIN
   SELECT * FROM inserted
END
GO
-- create index
CREATE CLUSTERED INDEX [idx_Title] ON [dbo].[employees]
(
   [Title] 
);
GO
-- create statistic
CREATE STATISTICS [MyStat] ON [dbo].[employees]([LastName])
GO
 
SELECT name AS [Trigger]     FROM sys.triggers WHERE name LIKE 'tr_employees';
SELECT name AS [Index]     FROM sys.indexes    WHERE name LIKE 'idx_Title';
SELECT name AS [Statistic] FROM sys.stats      WHERE name LIKE 'MyStat';
GO

Above we can see they are now here again.

To summarize, the view, stored procedure and function that query the table, but are not directly tied to it remain but stop functioning after the table is dropped. And the trigger, index, and statistic that are built on the table are dropped along with the table. Keep in mind this DROP TABLE IF EXISTS syntax works for other relational databases including MySQL and PostgreSQL. However, Oracle does not support this command.

Next Steps

Here are links to more MSSQLTips with some further information:

And here are links to some Microsoft Docs documentation:

Resources

Community

Subscribe