DRop table in sql server

Problem

You may be deploying a table to a 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?

Solution

The solution is to add conditional logic to your T-SQL to check if the 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:

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

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

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 exist.

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 polulate MyTable2
CREATE TABLE [dbo].[MyTable2] ([Col1] [int] NULL);
GO
INSERT INTO [dbo].[MyTable2](Col1) VALUES(1);
GO

-- create and polulate MyTable3
CREATE TABLE [dbo].[MyTable3]([Col1] [int] NULL);
GO
INSERT INTO [dbo].[MyTable3](Col1) VALUES(1);
GO

-- create and polulate 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.

-- 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