How to Drop Table If Exists in SQL Server

by

In this SQL Server tutorial, I will show you how to drop a table if it exists in SQL Server.

As a database developer, whenever the business logic changes, sometimes table objects become unnecessary, so removing these unnecessary objects is very important.

In this tutorial, I have explained how to drop one of the objects, such as a table, from the database using the DROP TABLE IF EXISTS command. Additionally, I have explained why you should use this command instead of the simple ‘DROP TABLE’ command.

Drop Table If Exists In SQL Server

SQL Server has the command ‘DROP TABLE IF EXISTS‘, which checks the table’s existence before dropping it. This means that if you drop a table that doesn’t exist in your database, it will raise an error.

So here, the command ‘DROP TABLE IF EXISTS’ maintains the flow of the database management process without showing any error if an attempt is made to drop a table that doesn’t exist in the database.

The syntax is given below.

DROP TABLE IF EXISTS table_name;

Where,

  • DROP TABLE: It is the command to delete the specified table from your desired database.
  • IF EXISTS: This clause is specified after the ‘DROP TABLE’ command to ensure that only the existing table is dropped without showing any error.
  • table_name: Name of the table that you want to drop.

Let’s see an example, but before that, I want to show all the tables in the web database. Run the query below to view all the tables in your current database.

SELECT name FROM SYSOBJECTS
WHERE xtype = 'U';
View All the Table Before Executing Drop Table If Exists Command

From the output, you can see two tables in my database: ‘ Customers’ and ‘Departments’.

Suppose you no longer have the ‘Customers’ table. You can drop that table using the command below.

DROP TABLE IF EXISTS Customers;

After executing the above command, the table ‘Customers’ is dropped from your database, which you can view using the query below.

SELECT name FROM SYSOBJECTS
WHERE xtype = 'U';
Drop Table If Exists In SQL Server

Look, the ‘Customers’ table has been deleted, and only one table remains, ‘Departments’.

If you remember, I told you that ‘DROP TABLE IF EXISTS’ prevents errors, and maybe you are wondering what kind of error prevents this command.

Let me show you. You just deleted the ‘Customers’ table; what will happen if you delete it again using only the ‘DROP TABLE’ command without the ‘IF EXISTS’ clause?

For a practical example, let’s execute the below command to delete the table ‘Customers’, which doesn’t exist.

DROP TABLE Customers;
Drop Table In SQL Server Which Doesn't Exist

Look in the output; it shows the error for the non-existent table, ‘Cannot drop the table ‘Customers’ because it does not exist or you do not have permission’.

This error can interrupt the flow of the query’s execution, so you must have a way to handle it. The clause ‘IF EXIST’ helps prevent this error when used with ‘DROP TABLE’ commands.

Again, execute the same query, but this time, add the ‘IF EXISTS’ clause as shown in the query below.

DROP TABLE IF EXISTS Customers;

The above command prevents errors and executes the commands successfully, thus maintaining the database management process.

I hope you understand how to drop a table if it exists in SQL Server.

Conclusion

In this SQL Server tutorial, you learned how to drop a table if it exists in SQL Server. In detail, you learned why to use the command ‘DROP TABLE IF EXISTS’ and the difference between the commands ‘DROP TABLE’ and ‘DROP TABLE IF EXISTS’.

You may like to read: