Dropping a Table in SQL: Step-by-Step Guide

The SQL DROP TABLE statement is used to remove a table definition and all the data, indexes, triggers, constraints and permission specifications for that table.

Note: Be careful before dropping a table. Deleting a table will result in loss of complete information stored in the table!

Syntax

The following SQL statement is the syntax for DROP TABLE

DROP TABLE table_name;

Example

A Human table already present in my database. Let us first verify the Human table and then we will delete it from the database as shown below ?

SQL> DESC Human;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(38)
 FIRST_NAME                                NOT NULL VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(20)
 AGE                                       NOT NULL NUMBER(38)
 ADDRESS                                            CHAR(25)
 SALARY                                             NUMBER(18,2)

This means that the Human table is available in the database, so let us now drop it as shown below.

Now Drop the table using DROP TABLE table_name query.

  SQL> DROP TABLE Human;

  Table dropped.

Here it is, our Human table is deleted. Now Let us verify again the Human table.

This means that the Human table is not available in the database. for that it's showing us object Human does not exist.

Another Examples

Below tables are already created in my database. Now I want to delete below tables. Let's start.

Drop table Persons

  SQL> DROP TABLE Persons;

  Table dropped.

Drop table Client_master_21

  SQL> DROP TABLE Client_master_21;

  Table dropped.

Drop table Product_master_21

  SQL> DROP TABLE Product_master_21;

  Table dropped.

Drop table

  SQL> DROP TABLE Salesman_master_21;

  Table dropped.