O'Reilly logo

SQL in a Nutshell, 3rd Edition by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Name

CREATE SCHEMA Statement

This statement creates a schema—i.e., a named group of related objects. A schema is a collection of tables, views, and permissions granted to specific users or roles. According to the ANSI standard, specific object permissions are not schema objects in themselves and do not belong to a specific schema. However, roles are sets of privileges that do belong to a schema.

Platform

Command

MySQL

Supported (as CREATE DATABASE)

Oracle

Supported, with variations

PostgreSQL

Not supported

SQL Server

Supported, with limitations

SQL2003 Syntax

CREATE SCHEMA [schema_name] [AUTHORIZATION owner_name]
[DEFAULT CHARACTER SET char_set_name]
[PATH schema_name[, ...]]
   [ ANSI CREATE statements [...] ]
   [ ANSI GRANT statements [...]  ]

Keywords

CREATE SCHEMA [schema_name]

Creates a schema called schema_name. When omitted, the database will create a schema name for you using the name of the user who owns the schema.

AUTHORIZATION owner_name

Specifies the user who will be the owner of the schema. When this clause is omitted, the current user is set as the owner. The ANSI standard allows you to omit either the schema_name or the AUTHORIZATION clause, or to use them both together.

DEFAULT CHARACTER SET char_set_name

Declares a default character set of char_set_name for all objects created within the schema.

PATH schema_name[, . . . ]

Optionally declares a file path and filename for any unqualified routines (i.e., stored procedures, user-defined functions, user-defined methods) in the schema.

ANSI CREATE ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required