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.