|
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.
When you drop a table, its definition is removed from the database. If there are dependent, non-materialized 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 Altering 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 Creating 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.
Connect to the database as a DBA user or as the owner of the table.
If you are dropping a table on which materialized views depend, disable each materialized view as follows:
Open the Views folder.
In the left pane, select the materialized view.
Select File > Disable.
Open the Tables folder for that database.
Select the table and then choose Edit > Delete.
Connect to the database as a DBA user or as the owner of the table.
If you are dropping a table on which materialized views depend, disable each materialized view using the ALTER MATERIALIZED VIEW ... DISABLE statement.
Execute a DROP TABLE statement.
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.
For more information, see DROP statement.