SQL - DROP TABLE

The SQL DROP TABLE statement is used to delete a table from the database. It drops all the data, indexes, triggers, constraints and permission specifications for the specified table.

Note: Be careful before dropping a table. Once deleted, all information stored in that table will be lost forever!.

Syntax

The syntax of using DROP TABLE statement is given below:

DROP TABLE table_name;

Example:

Consider a database containing a table called Employee with the following records:

EmpID	Name	City	Age	Salary
1	John	London	25	3000
2	Marry	New York	24	2750
3	Jo	Paris	27	2800
4	Kim	Amsterdam	30	3100
5	Ramesh	New Delhi	28	3000
6	Huang	Beijing	28	2800

In databases like MySQL and MariaDB, the description of the table can be checked using DESC command as shown below:

DESC Employee;

This will produce the result as shown below:

Field	Type	Null	Key	Default
EmpID	int(11)	No	PRI
Name	varchar(255)	No
City	varchar(100)	Yes		NULL
Age	int(11)	Yes		NULL
Salary	decimal(18,2)	Yes		NULL

Similarly in SQL Server, the table can be checked using EXEC sp_help command as shown below:

EXEC sp_help Employee;

This will produce the result as shown below:

Name	Owner	Type	Created_datetime
Employee	dbo	user table	2019-10-21 10:18:45.477

To delete this table, the SQL code is shown below:

DROP TABLE Employee;

After dropping the table, the DESC command (in MySQL & MariaDB) will throw following error:

DESC Employee;	
Result: Table 'testDB.Employee' doesn't exist

Whereas the EXEC sp_help command (in SQL Server) will throw following error:

EXEC sp_help Employee;	
Result: The object 'Employee' does not exist in database 'testDB' or is invalid for this operation.

Here, testDB is the name of database which initially had Employee table.