SQL in a Nutshell

Name

CREATE SCHEMA

Synopsis

This statement creates a schema — i.e., a named group of related objects. A schema is a collection of tables, views, and their associated permissions. The schema is associated with an existing, valid user ID (called the owner ).

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

SQL99 Syntax and Description

CREATE SCHEMA [schema_name] [AUTHORIZATION owner_name]
[DEFAULT CHARACTER SET char_set_name]
[PATH schema_name [,...n] ]

   [ <create_table_statement1> [...n] ]
   [ <create_view_statement1>  [...n] ]
   [ <grant statement1> [...n] ]

The CREATE SCHEMA statement is a container that can hold many other CREATE and GRANT statements. As an option, a DEFAULT CHARACTER SET names the schema’s default character set. The PATH also may be declared for any objects in the schema that reside on the filesystem.

Microsoft SQL Server and Oracle Syntax

CREATE SCHEMA AUTHORIZATION owner_name
   [ <create_table_statement1> [...n] ]
   [ <create_view_statement1>  [...n] ]
   [ <grant statement1> [...n] ]

If any statement fails within the CREATE SCHEMA statement, then the entire statement fails. One good thing about CREATE SCHEMA is that the objects within do not need to be organized according to any dependency. For example, a GRANT statement normally could not be issued for a table that does not exist yet. However, all the GRANT statements first could be placed in the CREATE SCHEMA statement, followed by the CREATE statements ...