Sometimes it is necessary to delete a table that is no longer in use.
To do this, use the following DROP TABLE statement, depending on the
syntax:
DROP TABLE [IF EXISTS] [database_name.] schema_name.]table_name;
In the above syntax you will find the following:
First, it specifies the name of the table to be dropped.
Second, it specifies the name of the database in which the table was
created and the name of the schema to which the table belongs.
IMPORTANT: The database name is optional, if you omit it, the DROP
TABLE statement will drop the table in the currently connected database.
Third, it uses the IF EXISTS clause to drop the table only if it exists.
The IF EXISTS clause is supported as of SQL Server 2016 13.x.
If you delete a table that doesn’t exist, you’ll get an error.
The IF EXISTS clause conditionally deletes the table if it already
exists.
When SQL Server drops a table, it also removes all data, triggers,
restrictions, and permissions from that table.
Also, SQL Server does not explicitly drop views and stored procedures
that reference the dropped table.
Therefore, to explicitly drop these dependent objects, you must use the
DROP VIEW and DROP PROCEDURE statements.
Additionally, SQL Server allows you to drop multiple tables at once using
a single DROP TABLE statement as follows:
DROP TABLE [database_name.][schema_name.]table_name_1,
[schema_name.]table_name_2, …
[schema_name.]table_name_n;
Examples
Delete a table that does not exist
The following statement deletes a table named revenues in the sample
database sales schema:
DROP TABLE IF EXISTS sales.revenues;
It is evident that no table or record will be affected, since it does not
exist. This, thanks to the IF EXISTS condition.
Example of deleting a single table
The following statement creates a new table with CREATE TABLE named
flights in the sales schema:
CREATE TABLE transit.flights (
flight_id INT PRIMARY KEY,
flight_note VARCHAR (255) NOT NULL,
flight_date DATE NOT NULL
);
To remove the flight table, use the following statement:
DROP TABLE transit.flight;
Drop a table with a foreign key constraint example
The following statement creates two new tables named airlines_groups and
suppliers in the airport schema:
CREATE SCHEMA airport;
CREATE TABLE `airlines` (
`airgroup_id` int(11) NOT NULL,
`airgroup_name` varchar(50) NOT NULL,
PRIMARY KEY (`airgroup_id`)
) ;
CREATE TABLE `suppliers` (
`supplier_id` int(11) NOT NULL,
`supplier_name` varchar(50) NOT NULL,
`group_id` int(11) NOT NULL,
PRIMARY KEY(`supplier_id`),
KEY `airgroup_id_idx` (`group_id`),
CONSTRAINT `airgroup_id` FOREIGN KEY (`group_id`) REFERENCES
`airlines` (`airgroup_id`));
Try to drop the suppliers table:
DROP TABLE airport.suppliers;
SQL Server returns the following error:
‘Cannot drop object ‘airport.suppliers’. There is a reference to it
in a FOREIGN KEY constraint’
SQL Server does not allow you to drop a table that is referenced by an
external constraint.
To drop this table, you must first drop the foreign key constraint or the
reference table.
In this case, you must first drop the foreign key constraint on either
the suppliers table or the suppliers table before dropping the
supplier_groups table.
DROP TABLE airport.suppliers_group;
DROP TABLE airport.suppliers;
If you use a single DROP TABLE statement to drop both tables, the
reference table must come first as shown in the query below:
DROP TABLE airport.suppliers, airport.supplier_groups;
Supplies to follow this tutorial
To run the examples featured here, you must have the following software
packages: