SQL DROP TABLE

The DROP TABLE command in SQL permanently removes a table from the database, including its data, structure, and related objects like indexes and triggers. This operation is irreversible, meaning the table and its contents cannot be recovered unless a backup exists.

Syntax:

DROP TABLE table_name;

Example

To demonstrate how to use the DROP TABLE command, let's first create a database and a table, and then we will drop it.

Step1: Create a Database and Table

First, we will create a database and table on which the SQL queries will be run.

CREATE DATABASE NewCafe;
USE NewCafe;

CREATE TABLE categories (
    CategoryID INT NOT NULL PRIMARY KEY, 
    CategoryName NVARCHAR(50) NOT NULL,
    ItemDescription NVARCHAR(50) NOT NULL
);

INSERT INTO categories (CategoryID, CategoryName, ItemDescription)
VALUES
(1, 'Beverages', 'Soft Drinks'),
(2, 'Condiments', 'Sweet and Savory Sauces'), 
(3, 'Confections', 'Sweet Breads');

SELECT * FROM categories;

Output:

At this point, the categories table has been created with three rows of sample data.

Step2: Drop the Table

Now, let’s use the DROP TABLE statement to delete the categories table permanently

Query:

DROP TABLE categories;

Output:

How SQL DROP TABLE Works

1. The SQL DROP TABLE statement is used to delete tables in a database, along with all associated data, indexes, triggers, constraints and permission specifications.

2. The table will be permanently disable, so use this query with caution.

3. Use DROP TABLE IF EXISTS query to prevent errors when dropping a table that does not exist. This command ensures that the drop operation only occurs if the table exists in the database.

DROP TABLE IF EXISTS categories;

4. When dropping a partitioned table, the DROP TABLE statement removes the table definition, all partitions, all data stored in those partitions, and all partition definitions. This operation also removes the partitioning scheme if no other tables use it.

5. The DROP TABLE statement can be used to drop temporary tables by including the TEMPORARY keyword.

DROP TEMPORARY TABLE temp_table_name;

6. To verify if a table is dropped, you can use the SHOW TABLES (MySQL) or SELECT * FROM INFORMATION_SCHEMA.TABLES (SQL Server, PostgreSQL) commands. Alternatively, in some systems, the DESC or DESCRIBE command can be used to check the table structure, but it will return an error if the table no longer exists.

SHOW TABLES;