Understanding DROP TABLE IF EXISTS SQL Statement

The T SQL DROP TABLE IF EXISTS statement is used to drop existing database objects. In this article, we are going to study the various use cases for this command and illustrate them with practical examples.

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.

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.

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 is a specialized means that in SQL check if table exists in the stored procedure (that particular table we want to drop). If it exists, 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

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

Database Objects	Dynamic management view
Tables	sys.tables
Columns	sys.column
Stored procedures	sys.procedures
Triggers	sys.triggers, sys.server_triggers
Constraints	sys.objects, sys.default_constraints
Views	sys.views

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

Use DROP [OBJECT] IF EXISTS

We see that there is an efficient way — DROP temp table if exists — to remove any temp table that we don’t need anymore. What about specific database objects?

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. Thus, 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 T SQL 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 use the following SQL DROP TABLE query variation:

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

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 DROP command in SQL 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

Summary

Now, we’ve learned about the DROP TABLE IF EXIST SQL Server statement and its usage in different scenarios and with various elements. The practical examples illustrated the most common use cases, and we hope that this information will be of help to you. Note that there is also the MySQL DROP TABLE IF EXISTS variant, and we’ll get to it in the future.

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.

Tags: sql database, sql drop table, t-sql, t-sql statements Last modified: October 07, 2022