DROP TABLE statement in SQL

DROP TABLE is a SQL statement used to delete tables along with all their data, indexes, triggers, constraints and permissions. The basic statement is very simple:

DROP TABLE tableName

However, we must take into account several important considerations that we will see with examples below:

  1. DROP TABLE admits different arguments to make the statement more precise. IF EXISTS only allows the statement to be executed if the table that we want to delete actually exists on the system. We can also specify the database and/or the schema to which the table we want to delete belongs. We will use the Orders table that we created in the CREATE TABLE section as an example:
DROP TABLE IF EXISTS store.dbo.Orders;

Now if we tried to execute the statement again without including IF EXISTS, the system would return the following error since the table doesn’t exist:

Cannot drop the table ‘store.dbo.Orders’, because it does not exist or you do not have permission.


IF EXISTS can also be used in statements like CREATE and/or ALTER and its use is recommended to avoid errors that can paralyze the flow of code for stored procedures, transactions, etc.

  1. A table can not be deleted if one of its fields refers to a Foreign Key of another table. In that case, we must remove the Foreign Key constraint from the related table or remove that table first in order to drop the first one.

For example starting again from the tables Customers and Orders, if we wanted to delete the Customers table with this statement, it will return the following error:

DROP TABLE IF EXISTS Customers;

Could not drop object ‘Customers’ because it is referenced by a FOREIGN KEY constraint.

This error is due to customerId being referenced as a Foreign Key in the Orders table. In this case to be able to drop the Customers table we would have to options: Drop first the Orders table or delete the Foreign Key constraint of said table using the ALTER TABLE statement.

  1. DROP TABLE admits more than one table as arguments to eliminate several tables in the same statement. If there are related tables, the referenced table where the Foreign Key is defined must be written first. In case we wanted to remove the Customers and Orders tables, Orders must go first so it doesn’t return the above error regarding the Foreign Key:
DROP TABLE IF EXISTS dbo.Orders, dbo.Customers;

DROP TABLE can also be used to delete temporary tables.