DROP TABLE Statement

Removes a table from the database.

Category: Data Definition
Supports: EXECUTE Statement
Data source: SAS data set, SPD Engine data set, SPD Server table, SASHDAT file, Aster, DB2 under UNIX and PC, Greenplum, HAWQ, HDMD, Hive, Impala, MDS, MySQL, Netezza, ODBC, Oracle, PostgreSQL, SAP HANA, Sybase IQ, Teradata

Syntax

DROP TABLE table [FORCE];

Arguments

table

specifies the name of the table to be removed.

Restriction You cannot alter or drop an MDS table while it is referenced in another transaction or statement. If a request fails, make sure other users are no longer using the table or have disconnected.

FORCE

specifies that the table is dropped without error processing. Use the FORCE keyword only when you are certain that dropping the table without error processing is what you want to do.

Details

The DROP TABLE statement removes a table definition and all the table's data, metadata, and indexes.
If you use the DELETE statement to remove all the rows in a table, the table still exists until it is removed with the DROP TABLE statement.
You cannot use the DROP TABLE statement to remove a table that is referenced by a foreign key constraint. You must drop the foreign key constraint first, and then remove the table.
If you remove a table with indexed columns, then all the indexes are automatically removed. If you drop a composite index, then the index is removed for all the columns that are named in that index.
You should delete references in queries to any tables that you remove.
Teradata users: See Usage Notes.

Comparisons

The DELETE statement deletes only the rows (data) in a table. The DROP TABLE statement removes the table from the database completely, including the table structure.
Last updated: February 23, 2017