SQL Tutorial SQL Advanced SQL Database SQL References

SQL DROP TABLE Keyword



The SQL DROP TABLE keyword 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 keyword is given below:

DROP TABLE table_name;

Example:

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

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

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:

FieldTypeNullKeyDefaultExtra
EmpIDint(11)NoPRI
Namevarchar(255)No
Cityvarchar(100)YesNULL
Ageint(11)YesNULL
Salarydecimal(18,2)YesNULL

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:

NameOwnerTypeCreated_datetime
Employeedbouser table2019-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.


❮ SQL Keywords