Using the DROP TABLE Statement to Remove a Table from the Database

When you no longer need a table, use the DROP TABLE statement to remove it from the database. Before dropping a table, however, make sure that you no longer need it! When the DBMS executes the DROP TABLE statement, it erases the table data and index(es) from the database and removes the definition of the table and its constraints from the system tables. Thus, the only way to recover a dropped table is to re-create the table and restore data by reading it from the most recent backup. As such, make sure you really no longer need the table or its data before you execute the DROP TABLE statement.

The syntax of the DROP TABLE statement is:

DROP TABLE

[[<schema name>.]<table owner name>.]]<table name>

Thus, to drop the CUSTOMER table in Tip 62, "Using the CREATE TABLE Statement to Assign Foreign Key Constraints," you would execute the SQL statement:

DROP TABLE customer

When it receives the DROP TABLE command, the DBMS checks to see if the table you want to drop is referenced by a FOREIGN KEY in another table. If it is (as is the case with CUSTOMER in the current example), the DROP TABLE statement will fail, and the DBMS will display an error message similar to:

Server: Msg 3726, Level 16, State 1, Line1

Could not drop object 'customer' because it is referenced

by a FOREIGN KEY constraint

Before you can remove a table reference by a FOREIGN KEY, you must first use the ALTER TABLE statement to remove the FOREIGN KEY constraint from the other table. In the current example, you must execute the ALTER TABLE statement

ALTER TABLE order DROP CONSTRAINT fk_customer_table

before the DBMS will let you drop the CUSTOMER table.

Note While the DBMS checks its system tables for FOREIGN KEY references to the table you want to remove, it does not check VIEWs (which you learned about in Tip 11, "Understanding Views") and stored procedures to see if they reference the table or its columns. Stored procedures that reference a dropped table will fail to run, and the DBMS will return an error message in place of VIEWs data that includes columns from a dropped table. As such, check your database documentation carefully to make sure that you are the only one that uses the table you are about to drop-before you drop the table.