SQL/MP Installation and Management Guide

Adding, Altering, Removing, and Renaming
Database Objects
HP NonStop SQL/MP Installation and Management Guide—523353-004
7-30
Dropping Tables
Dropping Tables
To drop a table, use the DROP TABLE statement. Dropping a base table with
dependencies is essentially dropping each of the dependent objects separately. SQL
drops all the dependencies automatically. These guidelines apply:
•
To have the authority to drop a table, you must have all the security and authority
required to drop or invalidate all dependent objects, including access to all the
catalogs describing all the dependent objects.
•
When you drop a table, the operation invalidates the programs that depend on that
table. Dropping a table can be very complicated if the table has many dependent
objects.
•
Dropping a table also drops the table definition and the definitions of all dependent
indexes, views, constraints, and comments from the data dictionary. To re-create
the environment, you must recover these definitions from backup tapes or OBEY
recovery files.
•
Dropping a table does not drop any collations used by the table columns.
To drop a table, follow these steps:
1. Start an SQLCI session. Enter a LOG command to initiate a log file for the
statements and commands entered in this session. Keep the log for your records.
2. Determine all the dependent objects of the table by using the DISPLAY USE OF
command.
3. Prevent all access to the table and its dependent objects.
4. Enter the DROP TABLE statement.
If you plan to use the TMF subsystem for recovering an audited SQL table, see
Recovering Purged SQL Tables on page 11-14 before proceeding.
Dropping Views
Dropping a view with the DROP VIEW statement is similar to dropping a table,
because the operation drops all the dependent views and invalidates all programs that
use the view. A view, however, contains no physical data.
Dropping a view with dependencies is essentially the same as dropping each of the
dependent objects separately. SQL drops all the dependent objects automatically, but
not dependent programs. These guidelines apply:
•
The DROP VIEW statement does not affect any underlying tables; indexes,
constraints, or programs that use the underlying tables; or collations used by view
or table columns. Likewise, views that are not dependent on the view you are
dropping are not affected.