Schemas

Schemas

None of our "big three" vendors has syntax to change a schema; only DB2 allows you to drop schema. Here is the syntax to do this:

DROP SCHEMA <schema_name> RESTRICT

The schema must be empty (i.e., no objects can be defined in it) for the schema to be deleted from the database. The following statement would fail unless you dropped the ACMETEST.ADDRESS and ACMETEST.SALESMAN tables (assuming examples from Chapter 4):

DROP SCHEMA acmetest RESTRICT
The object type "SCHEMA" cannot be dropped because 
there is an object "ACMETEST.ADDRESS" of type "TABLE" which depends
on it. SQLSTATE=42893

DROP TABLE ACMETEST.ADDRESS
DB20000I  The SQL command completed successfully.

DROP TABLE ACMETEST.SALESMAN
DB20000I  The SQL command completed successfully.

DROP SCHEMA acmetest RESTRICT
DB20000I  The SQL command completed successfully.