SQL Book

DROP TABLE in SQL - the complete guide to deleting tables

In this article, we will explain the purpose of DROP TABLE in SQL. We will show examples of how you can use it to manage your database tables and then test your understanding with our quick interactive quiz.

What does drop table do in SQL?

The DROP TABLE statement is used to remove/delete a table from the database. It deletes both the data stored within the table and the table object itself.

It also deletes any table indexes, triggers, constraints, and permissions that have been defined on the table(s) you are deleting.

The DROP TABLE statement falls under a subset of SQL statements known as Data Definition Language (DDL). DDL statements manipulate objects in the database rather than the rows of data that are within the database tables.

Caution alert...

You should use the DROP TABLE statement with great care. The data in the table that is being dropped (deleted/removed) is not backed up in any way before the table is deleted.

Once a table has been dropped the only way to retrieve the data would be from a backup of the database.

Syntax

The basic syntax is as follows:

DROP TABLE Table_name [, Table2_name ...];

The Table_name argument that is passed to the statement is the name of the table that you want to remove.

You can also delete multiple tables by passing in further table names in a comma-separated list.

By default, the statement will look for the table in the current database. If you want to specify a different database you can do so by specifying the optional database name and database schema.

DROP TABLE [Database_name].[Schema_name].[Table_name]

e.g. The following statement deletes the 'CartArchive' table from the 'WebShop' database.

DROP TABLE [WebShop].[dbo].[CartArchive]

What is the difference between DROP and DELETE?

Newcomers to SQL often query what the difference is between the DROP and DELETE statements. The simple answer is that the DELETE statement deletes rows of data from within a table, whereas the DROP statement deletes the table itself. This has the impact of deleting all the rows of data within the table at the same time.

Unlike the DROP statement, the DELETE statement does not affect any indexes, triggers, constraints or permissions that have been specified for the table.

The DELETE statement can be used with an optional WHERE clause to only delete certain rows from a table.

DELETE FROM Customers WHERE Country = 'Canada';

The DROP TABLE statement does not have an optional WHERE clause.

How do I drop a table in SQL Server?

DROP TABLE Customers;

You can also drop temporary tables using the same method.

DROP TABLE #MyTempTable;

Rolling back DROP TABLE within a transaction

If you use DROP TABLE within a SQL Server transaction you can rollback the transaction to undo the DROP action. This will restore the table and any rows that existed in the table before it was dropped.

BEGIN TRAN
INSERT INTO Orders (CustomerId, OrderDate) VALUES (1, '01/01/2020');
DROP TABLE Orders;
ROLLBACK TRAN;
-- this will return the same as before the transaction
SELECT * FROM Orders;

How do I drop a table in MySQL?

The following example shows how to drop more than one table in MySQL using a single statement.

DROP TABLE Customers, Orders;

In MySQL you add the TEMPORARY keyword to specify that you want to drop a temporary table.

DROP TEMPORARY TABLE MyTempTable

What happens if you attempt to drop a table that doesn't exist?

If you attempt to drop a table that does not exist then you will receive an error. This could be a problem if you are using the statement inside a stored procedure or transaction.

You can use the DROP TABLE IF EXISTS variation to verify that the table does actually exist before attempting to drop it.

Can you drop a table that has a foreign key constraint?

Let's say you have a one to many relationship between 2 tables. In our example, we will have Customers table and an Orders table. Each customer can have many orders. We have defined a foreign key on our Orders table that references the Customers table.

CREATE TABLE [dbo].[Customers](
[Id] [int] IDENTITY(1,1) PRIMARY KEY,
 [Name] [varchar](50) NOT NULL,
 [Email] [varchar](75) NOT NULL
);
GO
CREATE TABLE [dbo].[Orders](
 [Id] [int] IDENTITY(1,1) PRIMARY KEY,
 [CustomerId] [int] NOT NULL,
 [OrderDate] [date] NOT NULLL
 FOREIGN KEY (CustomerId) REFERENCES Customers (Id)
);

This means that a record in the Orders table must have a related 'parent' record in the Customers table.

What happens if we try and remove the Customers table?

DROP TABLE Customers;

SQL Server will return an error

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

Does DROP TABLE also drop indexes?

When you drop a table the indexes for that table are also dropped (deleted).

This is true for Microsoft SQL Server, MySQL, and Oracle.

Does DROP TABLE also drop Triggers?

The Triggers that are associated with a table are also dropped when you drop the table.

This is true in SQL Server, Oracle, and Postgres.

Does DROP TABLE also drop associated views and stored procedures in SQL Server?

No, any views, stored procedures or user-defined functions that reference the table to be dropped are not dropped.

What would happen if we had defined a view that used a table that we had dropped? To continue the above example of Customers and Orders, we could create a view in SQL Server called vw_CustomerOrders that used both tables as follows:

SELECT dbo.Customers.Id AS CustomerId,
dbo.Customers.Name, 
dbo.Customers.Email, 
dbo.Orders.OrderDate, 
dbo.Orders.Id AS OrderId
FROM dbo.Customers INNER JOIN
 dbo.Orders ON dbo.Customers.Id = dbo.Orders.CustomerId

If we had dropped the Orders table by running

DROP TABLE Orders;

and then tried to call the view

SELECT * FROM vw_CustomerOrders;

We would get an error:

Invalid object name 'dbo.Orders'.
Could not use view or function 'vw_CustomerOrders' because of binding errors.

This means you should not try to use any stored procedures, views, or user-defined functions that reference a dropped table in your SQL code. If you do then you will get an error as the table they are trying to reference no longer exists.

Quick Quiz: Test your knowledge for

Question . Write the SQL to:

Join our newsletter

Subscribe to our free weekly newsletter and get exclusive SQL tips and articles straight to your inbox. 

 Your email address is only used to send you our newsletter.