SQL Server drop table if exists

by
sql server drop table if exists

In this tutorial, we will learn everything on the SQL Server drop table if exists. We will see, how to remove a table from the database if it exists. We will learn multiple ways of checking if the table is available in the database or not.

If you want to delete a table, it should exists in the database. You need to know how the table is stored in the database and what are the various ways to check that.

Moreover, we will learn multiple ways of dropping the table in the SQL Server database if it exists.

  • SQL Server Drop table using IF EXISTS
  • Drop a table using Object ID SQL Server
  • Drop table if exists in INFORMATION_SCHEMA.TABLES
  • Drop table using sys.tables System View
  • SQL Server drop table if exists temp
  • SQL Server drop table if exists create table
  • SQL Server drop table if exists not working
  • SQL Server drop table if exists cascade

For all the examples I have used SQL Server 2019 Express and SQL Server management studio.

SQL Server Drop table using IF EXISTS

In SQL Server, we can drop a table with the IF EXISTS clause. This is the simplest way to drop a table after verifying that it exists in the database. The benefit of using this clause is that we can drop a table only of it is available in the database. Let us see an example:

  • We have created a sample table dbo.SampleTable.
CREATE TABLE dbo.SampleTable(
Name nchar(20),
Class nchar(4),
Stream nchar(20),
ID int)
  • We will drop this with IF EXISTS clause.
USE [master]
GO
DROP TABLE IF EXISTS dbo.SampleTable;
sql server drop table if exists
Drop Table using IF EXISTS
  • The above query will drop the table dbo.SampleTable.
  • If you execute this query again, you will not get any error like the tables does not exist in the database.

Drop table using Object ID SQL Server

In this section, we will learn how we can drop a table if it exists in the database with the Object_ID built-in function.

When an object is created, it is assigned an object_id, which is used to refer to that particular object whenever the object is used.

Similarly, a table is also an object in the database. Therefore, it is also assigned an object_id when it is created. We can use a built-in function OBJECT_ID() to get the object_id of the table.

In this section, we will learn how we can use this object_id to check whether the table exists in the database or not, then drop the table if it exists.

  • We will use the same sample table which we used in the above section i.e. dbo.Sample.
  • Now let us check its existence in the database with the OBJECT_ID function and drop it.
USE [master]
GO
IF OBJECT_ID('dbo.SampleTable','U') IS NOT NULL
DROP TABLE dbo.SampleTable
  • We are using the OBJECT_ID function to retrieve the object_id of the table.
  • The OBJECT_ID function takes two parameters:
    • Name of the object whose object_id you want to retrieve.
    • Type of the object.
  • Here we have specified ‘U’ as object type which means the type table.
  • After executing the above code, the table will be dropped.

Drop table if exists in INFORMATION_SCHEMA.TABLES

In this section, we will learn how we can drop a table after checking whether it exists in the INFORMATION_SCHEMA.TABLES or not.

  • The INFORMATION_SCHEMA contains the metadata about the database and objects. It also contains information about the user privileges and all other information of a database schema.
  • The INFORMATION_SCHEMA.TABLES is a view in the database that contains information about the tables. We will check if the table is present in this view and then drop the table.
  • In the INFORMATION_SCHEMA.TABLES, the TABLE_NAME column contains the names of the table and TABLE_SCHEMA contains the schema names.

You can see the contents of the INFORMATION_SCHEMA.TABLES to know more by using the SELECT statement.

USE [master]
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES
  • Suppose we have table dbo.SampleTable that we want to drop.
  • dbo is the schema name and SampleTable table name.
  • The following code can be used to drop table after verifying the existence of the table in the database:
USE [master]
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'SampleTable' AND TABLE_SCHEMA = 'dbo')
    DROP TABLE dbo.SampleTable;

Hence, you might have learned about how you can drop a table using verification in the INFORMATION_SCHEMA.TABLES view.

Drop table using sys.tables System View

In this section, you will learn about sys.tables system view and how you can use it to verify if a table exists in the database or not. We will also see an example, in which we will drop a table after verifying its existence in the database.

The sys.tabels is a view that contains the information about the tables that are stored in the database. It contains information about tables like object_id, information about the schema, information about when the table is created and much more.

You can see the contents of this view if you want to with the following query:

USE [master]
GO
SELECT * FROM sys.tables
  • Let us see how we can use this view to drop the table.
  • Consider we have a table dbo.SampleTable and we want to check if it exists in the database and then drop it.
USE [master]
GO
IF EXISTS(SELECT * FROM sys.tables 
WHERE SCHEMA_NAME(schema_id) LIKE 'dbo' AND name like 'SampleTable')  
   DROP TABLE dbo.SampleTable
  • The above query will drop the table if it exists in the database.

SQL Server drop table if exists temp

Temporary tables are the tables in the tempdb database that are temporarily created for the current session. The tables are dropped automatically once the session is over. This means, these temporary tables are not permanent in the database.

In this section, we will learn how we can drop a temporary table from the database if it exists. We will learn this with the help of an example. We will create a temporary table in the database, then drop it.

  • Let us create a temporary table in the tempdb database.
USE [tempdb]
CREATE TABLE dbo.#SampleTable(
Name nchar(20),
Class nchar(4),
Stream nchar(20),
ID int)
  • You can insert data into the temporary table if you want. We will not insert any data into it for this example.
  • You can see the temporary tables under the tempdb database in the Object Explorer Window.
SQL Server drop table if exists temp
Temporary Tables
  • Now write the following SQL code and execute it:
USE [tempdb]
GO
IF OBJECT_ID('tempdb..#SampleTable') IS NOT NULL
    DROP TABLE #SampleTable
GO
  • The above code will check the object_id of the temporary table in the database and drop the table if it already exists.
  • You can verify if the table is dropped in the Object Explorer Window. Refresh the tempdb database and you can see there is no table with the name #SampleTable.

At this point, you might have understood how we can drop a temp table if it exists in the SQL Server database.

Read: SQL Server Create Temp Table

SQL Server drop table if exists create table

In real-life scenarios, you would have faced a situation, where you wanted to recreate a table instead of modifying its structure. In such situations, you have to drop the table first and then create it again.

Also, sometimes you want to create a table but do not know if you have already created a table with the same name. You will end up with an error saying that there is already an object with the same name in the database.

Therefore, it is always a good practice to wite the DROP TABLE statement before creating the table.

In this section, you will learn how you can verify the existence of the table in the database and create a table with the same name after dropping the already existing table.

You will see an example where we will be implementing the above method to create a new table.

  • Suppose you have to create a table SampleTable and insert values into it. But you mistakenly forgot to enclose the date values inside the quotes, resulting in an error.
USE [master]
GO
CREATE TABLE dbo.SampleTable(
OrderID int PRIMARY KEY,
CustomerID int,
SalesManID int,
OrderDate date)

INSERT INTO dbo.SampleTable(OrderID,CustomerID,SalesManID,OrderDate)
VALUES(1101,2201,4301,2021-06-14),
	  (1102,2201,4301,2021-06-14),
	  (1103,2203,4302,2021-05-19),
	  (1104,2206,4304,2021-05-21),
	  (1105,2308,4310,2021-04-30)
SELECT * FROM dbo.SampleTable
  • This time you corrected your mistake and enclosed dates inside the quotes.
USE [master]
GO
CREATE TABLE dbo.SampleTable(
OrderID int PRIMARY KEY,
CustomerID int,
SalesManID int,
OrderDate date)

INSERT INTO dbo.SampleTable
	  (OrderID,CustomerID,SalesManID,OrderDate)
VALUES(1101,2201,4301,'2021-06-14'),
	  (1102,2201,4301,'2021-06-14'),
	  (1103,2203,4302,'2021-05-19'),
	  (1104,2206,4304,'2021-05-21'),
	  (1105,2308,4310,'2021-04-30')
SELECT * FROM dbo.SampleTable
SQL Server drop table if exists create table
Resulted in Error
  • The code resulted in an error because you have already created the table.
  • Therefore, it is always a good practice to first drop the table if it exists.
  • You can use the following code snippet before the CREATE TABLE statement to drop the table.
IF OBJECT_ID('dbo.SampleTable','U') IS NOT NULL 
DROP TABLE dbo.SampleTable	

CREATE TABLE dbo.SampleTable(
OrderID int PRIMARY KEY,
CustomerID int,
SalesManID int,
OrderDate date)

INSERT INTO dbo.SampleTable
	  (OrderID,CustomerID,SalesManID,OrderDate)
VALUES(1101,2201,4301,'2021-06-14'),
	  (1102,2201,4301,'2021-06-14'),
	  (1103,2203,4302,'2021-05-19'),
	  (1104,2206,4304,'2021-05-21'),
	  (1105,2308,4310,'2021-04-30')
SELECT * FROM dbo.SampleTable
  • This time you will not get any error.
SQL drop table if exists create table
Table Created

Hence, you might have learned why we need to drop the table before creating it and how to do it.

SQL Server drop table if exists not working

There are some situations when we are trying to drop a table and get an error. Most probably, there should be two errors that you could have faced while dropping the table. When you have to drop a table, you need to care about two things:

  1. Permissions to read and write the table: If you have not logged in as an administrator but use a restricted user access account, you may face problems dropping the table. Therefore, make sure that you have proper permissions to read and write changes in the database.
  2. Table Dependencies: Another reason could be the dependencies of the table. If you are trying to drop a table that has some references or dependencies from another table. In such conditions, you need to drop the constraints or references of the table or you have to first drop the table which is referring to the table you want to drop. For example, if you want to drop a table having a column referenced as a Foreign Key from another table, you cannot drop the table. First you have to delete the table in which the Foreign Key reference is defined.

SQL Server drop table if exists cascade

In the oracle server, you have the CASCADE CONSTRAINTS option which you can use with the DROP TABLE statement to drop the referential integrity constraints of the table.

But in SQL Server there is no such option to do it directly. However, you can use a script to do so. In this section, we will learn how we can use a script to drop the referential integrity constraints of a table.

The constraints of a table can be found in the INFORMATION_SCHEMA.TABLE_CONSTRAINTS view. We can use this view to drop the table constraints.

DECLARE @sql nvarchar(255)
WHILE EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_catalog = <database name> and table_name = <table name>)
BEGIN
    select    @sql = 'ALTER TABLE <table name>  DROP CONSTRAINT ' + CONSTRAINT_NAME 
    from    INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    where    constraint_catalog = <database name> and 
            table_name = <table name>
    exec    sp_executesql @sql
END

You may also like the following sql server tutorials:

Thus, in this tutorial, you have learned how to drop a table if it exists in the SQL Server database.

  • SQL Server Drop table using IF EXISTS
  • Drop a table using Object ID SQL Server
  • Drop table if exists in INFORMATION_SCHEMA.TABLES
  • Drop table using sys.tables System View
  • SQL Server drop table if exists temp
  • SQL Server drop table if exists create table
  • SQL Server drop table if exists not working
  • SQL Server drop table if exists cascade