Effects of the DROP TABLE Statement

Use the DROP TABLE statement with caution. When you remove a table, you also delete the data stored in it,any indexes or constraints on the columns (including all the referential constraints placed on its columns), any local synonyms assigned to it, any triggers created on it, and any access privileges granted on the table. You also drop all views based on the table and any violations and diagnostics tables associated with the table.

DROP TABLE does not drop any stored procedures that reference the dropped table, but the procedures that reference the dropped table (or synonyms for the dropped table, or views referencing the dropped table) will fail if it is run before a new table or view with the same name being (re)created.

DROP TABLE does not remove any synonyms for any table that was created in an external database. To remove external synonyms for the dropped table, you must do so explicitly with the DROP SYNONYM statement.

If the DROP TABLE is used with a synonym name, both the synonym and the table identified by the synonym are dropped along with any other synonyms that reference the same table.

You can prevent users from specifying a synonym in DROP TABLE statements by setting the USETABLENAME environment variable. If USETABLENAME is set, an error results if any user specifies a synonym name in a DROP TABLE statement.