SQL DROP TABLE

Sometimes we may decide that we need to get rid of a table in the database. In fact, it would be problematic if we cannot do so because this could create a maintenance nightmare for the DBA's. Fortunately, SQL allows us to do it, as we can use the DROP TABLE command.

The syntax for DROP TABLE is,

DROP TABLE "table_name";

If we wanted to drop the Customer table that we created in the CREATE TABLE section, we simply type,

DROP TABLE Customer;

Be careful when you use this command, as the table and any data stored in that table will be lost!

Drop Multiple Tables At The Same Time

It is possible to drop more than one table at a time. To do that, list the names of all the tables we wish to drop separated by comma after DROP TABLE. For example, if we want to drop the User_Details table and the Job_List table together, we can issue the following SQL statement:

DROP TABLE User_Details, Job_List;

IF EXISTS

When we attempt to drop a table that does not exist, an error will result. To prevent this type of error from happening, some databases such as MySQL and Oracle allow an optional "IF EXISTS" phrase between DROP TABLE and the table name(s). This tells the database to execute the DROP TABLE statement only if the table to be dropped already exists. If the table does not exist, nothing is executed and there is no error message. The following is an example of a DROP TABLE IF EXISTS statement:

DROP TABLE IF EXISTS Customer;