SQL DROP TABLE

SQL DROP TABLE

The SQL DROP TABLE statement is used to delete a table object and its data and related objects(primary key, foreign key, index, trigger and permission) .

This command needs to be executed carefuly as all the data will be lost within the deleted table and where it has been used as lookup column

Syntax
The basic syntax of this DROP TABLE statement is as follows −

DROP TABLE databasename ;

Example1
Lets assume we have tables called Users and Tasks with the following definition and data

Users

Definition
+---------+---------------+------+-----+
| Field     | Type             | Null   | Key |
+---------+---------------+------+-----+
| ID         | int                | NO   | PRI |
| NAME    | varchar(50)   | NO   |       |
+---------+---------------+------+-----+

Data
+---------+---------------+
| ID         | NAME           |
+---------+---------------+
| 1           | Thomas        |
| 2           | Martin          |
+---------+---------------+


Executing "DROP Table Users" statement will delete Users table definition and its related data and objects

Example2
Lets assume we have tables called Users and Tasks with the following definition and data

Users

Definition
+---------+---------------+------+-----+
| Field     | Type             | Null   | Key |
+---------+---------------+------+-----+
| ID         | int                | NO   | PRI |
| NAME    | varchar(50)   | NO   |       |
+---------+---------------+------+-----+

Data
+---------+---------------+
| ID         | NAME           |
+---------+---------------+
| 1           | Thomas        |
| 2           | Martin          |
+---------+---------------+

Tasks

Definition
+---------+---------------+------+-----+
| Field     | Type             | Null  | Key  |
+---------+---------------+------+-----+
| ID         | int               | NO    | PRI |
| UserID  | int                | NO   | FK   |
| NAME    | varchar(50)  | NO    |       |
+---------+---------------+------+-----+

Data
+---------+---------------+---------------+
| ID         | UserID         | NAME           |
+---------+---------------+---------------+
| 1          | 1                  | Task1          |
| 2          | 1                  | Task2           |
| 1          | 2                  | Task3           |
+---------+---------------+---------------+


Executing "DROP Table Users" statement will fail as there is a foreign key to Tasks table on the UserID column