How to Drop Table if Exists in SQL Server

Do you want to drop the table in SQL Server? No worries. Follow this simple SQL Server tutorial to delete a table in SQL Server Management Studio. This tutorial will teach you how to check the table’s existence before dropping it.

How to Drop Table if Exists in SQL Server

Let us start with the syntax followed by the example.

Syntax

The DROP TABLE command in SQL Server will effectively remove the table from the database, including its structure, all stored data, indexes, triggers, and constraints.

The syntax of the DROP TABLE command is,

DROP TABLE table_name;

Where table_name is the name of the table. If you want to remove the table from a different database, use the below syntax.

DROP TABLE [database_name].table_name;

Note – If the table has a foreign key relationship, direct deletion of the table is impossible. In this case, you have to delete the referencing table before deleting the actual table.

Moreover, the DROP TABLE command will not delete functions, stored procedures, or views that refer to the table we want to delete. We have to make sure the table’s deletion will not affect the functionality. You can choose to delete stored procedures and views if your project needs.

Example

Let’s see an example of dropping the PatientDetails table from the database. IF EXISTS will check if the table is present; otherwise, it will throw an error for the DROP Table Command, like ‘cannot DROP the table ‘name’ because it does not exist or you do not have permission.

DROP TABLE IF EXISTS PatientDetails;

Now, the table will deleted from the SQL Server database.

If the table is not present, then below is the message you will receive.

When you want to delete multiple tables, you do not need to run the DROP TABLE command separately for each table. We can include all the target tables in one command and separate the table names with commas.

Use the below query to delete multiple tables from the same database. Here, I am deleting the two tables, StudentDetails and Studetails, with a single command.

Drop Table StudentDetails, StuDetails; 

Once you delete it, if we are trying to see the table, then the below error we will get.

This is the method to delete multiple tables from the database.

How to drop temp table in SQL Server if exists

Temporary table—It is similar to Permanent Tables. Temporary tables are created in TempDB and deleted once the connection is stopped. They help you store and process the output and are very useful for storing temporary data. Below is the syntax for creating temporary tables.

CREATE TABLE #Students (Id INT, name VARCHAR(50))  

At that time, the DROP TABLE command is the solution for deleting the temporary table quickly.

See the code below to create, insert, and select the temporary table in SQL Server.

Create table #students (
ID int primary key,
name varchar (50),
);

Insert into  #students (Id, Name)
values (11, 'James'),
	   (12, 'peter');

	   select * from  #students

Approach-1 Using SQL Query

Use the syntax below to delete the table from the SQL Server.

 Drop table  #students

There are two types of temporary tables in SQL Server. Namely,

• Local temporary table
• Global temporary table

Local Temporary Table

A local temporary table is deleted when the database connection is disconnected. “#” is used as the prefix of a table name to create a local temporary table. Even if the temporary table is created inside the stored procedure also, it will be deleted automatically once the stored procedure execution is completed and closed. Even if you are opening the next new query page also, you cannot see the execution.

Create Table #LocalTemp
(
ID int,
Name varchar (50),
)
    Go
	Insert into #LocalTemp values (1, ' peter');
	Go
	Select * FROM #LocalTemp

If you want to drop the local temporary table, use the syntax below.

Drop Table #LocalTemp

Global Temporary Table

This is also a temporary table used with double hash ##. It won’t be stored in the database, but you can see the execution on another query page. This is the difference between a local temporary table and a global temporary table.

Create Table ##GlobalTemp
(
ID int,
Name varchar (50),
)
    Go
	Insert into #GlobalTemp values (1, ' peter');
	Go
	Select * FROM #GlobalTemp

If you want to drop the global temporary table, use the syntax below.

Drop Table ## GlobalTemp

Approach-2 Using SQL Server Management Studio

We have deleted and dropped the table in SQL Server through commands. Now, we will delete it through the GUI. This process will simplify the table deletion.

• Open SQL Server Management Studio.
• Here, expand the Tables to see all the tables you have created.
• Select the table you want to delete.
• Right-click the table and select Delete from the menu.

Now, the table will be deleted from the SQL Server database.

Conclusion

The DROP TABLE command in SQL Server is an essential feature for managing database objects. We have seen how to use this command through query and GUI.

One powerful client is dbForge Studio for SQL Server, which provides a range of functionality to handle all database tasks effectively in SQL Server. Similarly, using the DROP Table query is also a straightforward method.

However, be cautious while writing this command since it quickly deletes the tables. Understanding the consequences of this action is better for protecting the database’s consistency and integrity.

You may also like the following SQL Server tutorials:

• Trigger to insert data in another table in SQL Server
• Temp table in stored procedure in SQL Server
• Difference between table and view in SQL Server