Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 » SQL Anywhere Server - SQL Usage » Creating Databases » Working with database objects » Working with tables

 

Drop tables

This section describes how to drop tables from a database. You can use either Sybase Central or Interactive SQL to perform this task. In Interactive SQL, deleting a table is also called dropping it.

You cannot drop a table that is being used as an article in a SQL Remote publication. If you try to do this in Sybase Central, an error appears. Also, if you are dropping a table that has dependent views, there may be additional steps to make, as noted in the following sections.

Table deletions and view dependencies

When you drop a table, its definition is removed from the database. If there are dependent regular views, the database server attempts to recompile and re-enable them after you perform the table alteration. If it cannot, it is likely because the table deletion invalidated the definition for the view. In this case, you must correct the view definition. See Alter regular views.

If there are dependent materialized views, subsequent refreshing will fail because its definition is no longer valid. In this case, you must drop the materialized view and then create it again with a valid definition. See Create materialized views.

Before altering a table, you may want to determine whether there are views dependent on a table, using the sa_dependent_views system procedure. See sa_dependent_views system procedure.

For an overview of how table deletions affect view dependencies, see View dependencies.

To drop a table (Sybase Central)
  1. Connect to the database as a user with DBA authority, or as the owner of the table.

  2. If you are dropping a table on which materialized views depend, disable each materialized view:

    1. In the left pane, double-click Views.

    2. Right-click the materialized view and choose Disable.

  3. Double-click Tables.

  4. Right-click the table and choose Delete.

  5. Click Yes.

To drop a table (SQL)
  1. Connect to the database as a user with DBA authority, or as the owner of the table.

  2. If you are dropping a table on which materialized views depend, disable each materialized view using the ALTER MATERIALIZED VIEW ... DISABLE statement.

  3. Execute a DROP TABLE statement.

Example

The following DROP TABLE command deletes all the records in the Skills table and then removes the definition of the Skills table from the database

DROP TABLE Skills;

Like the CREATE statement, the DROP statement automatically executes a COMMIT statement before and after dropping the table. This makes all changes to the database since the last COMMIT or ROLLBACK permanent. The DROP statement also drops all indexes on the table. See DROP TABLE statement.