SQL DROP Table

SQL DROP Table statement is used to delete or drop a table from a database or SQL Schema.

DROP Table statement removes all the data, indexes, triggers, constraints along with the definition of the table. So while issuing this statement you should be very careful as all the information available to the table will be lost forever.

SQL DROP Table Syntax

Below is the basic syntax of SQL DROP Table.

DROP TABLE table_name;

Here, table_name specifies the name of the table which you want to delete.

SQL DROP Table Example

Let’s first verify the table name if the table exists in the database.

SQL> desc STUDENTS;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| ROLLNO  | int          | NO   | PRI | NULL    |       |
| NAME    | varchar(20)  | NO   |     | NULL    |       |
| AGE     | int          | NO   |     | NULL    |       |
| ADDRESS | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

So the table STUDENTS is available in the database. Now if you want to drop the STUDENTS table from the database, you can follow the below command.

SQL> drop table STUDENTS;
Query OK, 0 rows affected (0.52 sec)

The above message confirms that the STUDENTS table has been deleted from the database.

You can cross-check the fact by issuing DESC command now. 

SQL> desc STUDENTS;
ERROR 1146 (42S02): Table 'teradatapoint.students' doesn't exist

SQL DROP TABLE Example in MySQL

The following command is used to drop a table from MySQL database.

DROP TABLE table_name;

SQL DROP TABLE Example in SQL Server

The following command is used to drop a table from SQL Server database.

DROP TABLE table_name;

SQL DROP TABLE Example in ORACLE

The following command is used to drop a table from ORACLE database.

DROP TABLE table_name;

From the above examples, we can see that SQL DROP TABLE command is similar across MySQL, SQL Server and ORACLE database.