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 existence of the table before dropping it.

DROP Table Syntax

The DROP TABLE command in SQL Server will effectively remove the entire 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.

Drop-Table if Exists SQL Server

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;
Drop Table if exists SQL Server

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

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

Drop Table if Exists

How to Drop Multiple Tables from the Database

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; 
Drop Multiple Table

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

Drop Table in SQL Server

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

Drop Temporary Table from SQL Server

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 below code 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
Drop SQL Server Table

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

 Drop table  #students
Drop Table SQL Server

Types of Temporary Tables in SQL Server

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, you can also use the below syntax.

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, you can also use the below syntax.

Drop Table ## GlobalTemp

How to Drop Table in dbForge 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.
SQL Server Drop Table

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

Conclusion

The DROP TABLE command in SQL Server is an important 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 to choose.

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: