Understanding DROP TABLE IF EXISTS SQL Statement

The DROP TABLE IF EXISTS statement is used to drop existing database objects. When we create a complex stored procedure, we might also make several intermediate tables (physical tables or temp tables) to store and process the data. Now, we must add the code that drops those intermediate tables in the stored procedure. Otherwise, we might receive an error.

CodingSight - Understanding DROP TABLE IF EXISTS SQL Statement

Suppose we have created a stored procedure named sp_getStudentsData. Within it, we are creating an intermediate table named tbl_temp_Student to store and process the data of the tblStudent table. However, we don’t add the code to drop that temporary table.

When we execute the stored procedure for the first time, it will be successful. But when we execute it again, the following error occurs:

Msg 2714, Level 16, State 6, Procedure sp_getSalesData, Line 4 [Batch Start Line 0]
There is already an object named 'tbl_temp_Student' in the database.
Error when executing the stored procedure for the first time

To handle such errors, we can use the following options:

  • EXISTS statement
  • DROP [OBJECT] IF EXISTS

Let us consider them thoroughly.

Use EXISTS statement

The EXISTS statement checks if the table specified in the stored procedure exists. If so, then it should drop and recreate that table. If the table does not exist, this method should create the table.

To do that, we can add the following statement:

if exists (select 1 from sys.all_objects where object_name(object_id)='tbl_temp_Student')
begin
	Print 'The table exists, add a code to drop the table here.'
end
Go
Print 'Create the table'
Go
The EXISTS statement checks if the table specified in the stored procedure exists.

Note: To populate various database objects and their details, you can use any of the following DMVs

Database ObjectsDynamic management view
Tablessys.tables
Columnssys.column
Stored proceduressys.procedures
Triggerssys.triggers, sys.server_triggers
Constraintssys.objects, sys.default_constraints
Viewssys.views

Alternatively, you can use sys.all_objects to populate the database objects.

Use DROP [OBJECT] IF EXISTS

A new and extremely useful SQL Statement DROP OBJECT IF EXISTS has been introduced in SQL Server 2016. It drops the existing object specified in that statement.

The syntax of the DROP TABLE IF EXISTS is the following:

DROP OBJ_TYPE [ IF EXISTS ] OBJ_NAME
  • Obj_Type – specifies the object type and applies to any of the following objects:
    • Table
    • Column
    • Triggers
    • Views
    • Constraints
    • Database
  • Obj_name – specify the object name which you want to drop.

Now, we are going to examine the DROP [OBJECT TYPE] IF EXISTS in different use cases with examples.

Drop table using DROP IF EXISTS

Suppose you want to drop the tblStudent table. If you are using SQL Server 2014 and older version, you can run the following query:

if exists (select 1 from sys.tables where name='tblStudents')
drop table tblStudents
Go 
CREATE TABLE [dbo].[tblStudents](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[student_name] [varchar](250) NOT NULL,
	[student_code] [varchar](5) NOT NULL,
	[student_grade] [char](2) NOT NULL,
	[SchoolID] [int] NOT NULL)
GO

In the above example, our SQL Server queries sys.tables to locate the table. In case that the IF EXISTS condition evaluates TRUE, it will execute the DROP TABLE statement.

For the SQL Server 2016 and higher, we can drop the tblStudent table using the DROP TABLE IF EXISTS statement:

DROP TABLE  IF EXISTS [dbo].[tblStudents]
Go
CREATE TABLE [dbo].[tblStudents](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[student_name] [varchar](250) NOT NULL,
	[student_code] [varchar](5) NOT NULL,
	[student_grade] [char](2) NOT NULL,
	[SchoolID] [int] NOT NULL)
GO

Drop Temporary Table using DROP IF EXISTS

Suppose you want to drop the temporary table named #tblStudent. The temporary tables are created in tempdb. Thusm we must use the three-part name in the query:

if exists (select 1 from tempdb.sys.tables where name='tblStudents')
drop table #tblStudents
Go 
CREATE TABLE [#tblStudents](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[student_name] [varchar](250) NOT NULL,
	[student_code] [varchar](5) NOT NULL,
	[student_grade] [char](2) NOT NULL,
	[SchoolID] [int] NOT NULL)
GO

In the above example, the SQL Server queries Tempdb.sys.tables to locate the table. As mentioned, we must use the three-part name to locate the table (db_name.schema_name.tbl_name). In case the IF EXISTS condition evaluates TRUE, our query will execute the DROP TABLE statement.

In the SQL Server 2016, you can drop the tblStudent table using the DROP TABLE IF EXISTS statement.

DROP TABLE  IF EXISTS [#tblStudents]
Go
CREATE TABLE [#tblStudents](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[student_name] [varchar](250) NOT NULL,
	[student_code] [varchar](5) NOT NULL,
	[student_grade] [char](2) NOT NULL,
	[SchoolID] [int] NOT NULL)
GO

Drop column using DROP IF EXISTS

Suppose you want to drop a column named SchoolID from the tblStudent table. If you are using SQL Server 2014 or older, you can run the following query:

if exists (select 1 from sys.columns where name='SchoolID')
Alter table tblStudent drop column SchoolID
Go

In the SQL Server 2016 and later version, you can run the following query using the IF EXISTS statement to drop the column from the tblStudent table:

ALTER TABLE tblStudents DROP COLUMN IF EXISTS SchoolID

Drop Procedure using DROP IF EXISTS

Suppose you want to drop and create a new version of the stored procedure. The code of the stored procedure is the following:

create procedure sp_getstudents
as
begin
select student_name, student_code from tblstudents
end
Go

Run the below code to view the output:

Exec sp_getstudents
Drop Procedure using DROP IF EXISTS

Now, we want to alter the stored procedure by adding the student_grade column. In SQL Server 2014 and older, we can run the following query to recreate the stored procedure:

if exists (select 1 from sys.procedures where name='sp_getstudents')
drop procedure sp_getstudents
Go
create procedure sp_getstudents
as
begin
select student_name, student_code,student_grade from tblstudents
end
Go

In the SQL Server 2016 and later, we execute the following query using the IF EXISTS statement to recreate the stored procedure:

DROP Procedure IF EXISTS sp_getstudents
Go
create procedure sp_getstudents
as
begin
select student_name, student_code,student_grade from tblstudents
end
Go

Run the code to view the output of the stored procedure:

Exec sp_getstudents
IF EXISTS statement to recreate the stored procedure

Summary

Now, we’ve learned about the DROP IF EXISTS statement and its usage in different scenarios. The practical examples illustrated the most common use cases, and we hope that this information will be of help to you.

The value of this SQL statement could not be omitted by the modern tools for SQL Server that target simplifying and automating tasks. dbForge Schema Compare for SQL Server created by Devart provides this functionality in the Synchronization wizard. There, you can configure the tool to generate the IF EXISTS statement for different objects.

The Check for object existence option adds an additional check into the synchronization script (before each object DDL) to verify if the object in question exists in the target database.

Nisarg Upadhyay

Nisarg Upadhyay

Nisarg is a SQL Server Database Administrator and Microsoft certified professional who has more than 5 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the Bachelors in Information Technology from Ganpat University.

Leave a Reply

Your email address will not be published. Required fields are marked *