SQL DROP TABLE

In this tutorial, you will learn how to use SQL DROP TABLE statement to completely delete database table structure and data from a database.

SQL DROP TABLE is used to remove the whole database table in term of table structure and data. When you use SQL DROP TABLE, the database engine removes all objects related to that table including data, table definition, indexes, table constraints, trigger and permission spec of that table. The syntax of SQL DROP TABLE is as follows:

In order to remove table, you'll need to have sufficient permission to do so. In the statment you specify the table name you want to remove, the database will remove the table in the current working database. If you want to remove table in a different database you need to use the following syntax:

In the above syntax, you specify the database followed by a full stop and table name.

SQL DROP TABLE examples

Here is an example of removing table employees in the current working database:

To remove table employees in the database tmp, you'll need to execute the following query:

Remarks on SQL DROP TABLE

• Before executing the SQL DROP TABLE you need to to consider it seriously. You have to asked yourself whether the table being removed is still used in the future. If this is the case, you have to backup the database first before droping table.
• You should always find all the foreign key constraints or referencing table that reference to the table being removed. Then you remove all the references first before dropping table.
• It is safer to specify the database name when you use SQL DROP TABLE statement. Because in the database landscape you may have different database systems and names for development, testing and production systems. Sometimes you have to go to those systems at the same time to do the work. You may confuse the production, testing and development systems and potentially drop table in the production system with the thought that it is development system.
• If you just want to delete the data use SQL DELETE or SQL TRUNCATE statement instead.