SQL in a Nutshell, 2nd Edition

Name

SET SCHEMA Statement

Synopsis

The SET SCHEMA statement changes the value of the CURRENT SCHEMA setting to a user-specified schemas.

Platform	Command
DB2	Supported, with variations
MySQL	Not supported
Oracle	Not supported
PostgreSQL	Not supported
SQL Server	Not supported

SQL2003 Syntax

SET SCHEMA schema_name [,...]

Keywords

schema_name [,...]

Defines one or more schema as the current path.

Rules at a Glance

SET SCHEMA defines a user-defined schema to use to qualify an unqualified object, such as a table or view.

The following example sets the current schema for unqualified objects to scott:

SET SCHEMA scott;

Then, whenever an object is referenced during the current session, it will assume the scott schema, if no schema is identified.

Programming Tips and Gotchas

The SET SCHEMA statement cannot assign a schema from a remote database as the CURRENT SCHEMA.

SET SCHEMA does not apply the schema to unqualified routines like functions, procedures, and methods. It only applies to database objects like tables and views.

DB2

DB2 supports several variations on the ANSI standard:

SET [CURRENT] SCHEMA = {schema_name | USER } [,...]

where:

SET [CURRENT] SCHEMA =

Sets the path, with a slight variation in syntax from the ANSI standard. Note that the equal sign (=) is required.

schema_name

Defines a user-specified schema, of no more than 30 bytes in length, as the current schema. The schema_name may be a literal, a host variable, or a string constant.

USER

Defines the value of the USER special ...