CREATE SCHEMA Statement

Creates a schema, which is a collection of tables, views, and permissions and their associated permissions, for a database user.

Syntax

CREATE SCHEMA AUTHORIZATION userid
... [ { create-table-statement
| create-view-statement
| grant-statement } ] …

Usage

The userid must be the user ID of the current connection. You cannot create a schema for another user. The user ID is not case-sensitive.

If any of the statements in the CREATE SCHEMA statement fail, the entire CREATE SCHEMA statement is rolled back.

CREATE SCHEMA statement is simply a way to collect individual CREATE and GRANT statements into one operation. There is no SCHEMA database object created in the database, and to drop the objects you must use individual DROP TABLE or DROP VIEW statements. To revoke permissions, use a REVOKE statement for each permission granted.

Note: The CREATE SCHEMA statement is invalid on an active multiplex.

Individual CREATE or GRANT statements are not separated by statement delimiters. The statement delimiter marks the end of the CREATE SCHEMA statement itself.

The individual CREATE or GRANT statements must be ordered such that the objects are created before permissions are granted on them.

Creating more than one schema for a user is not recommended and might not be supported in future releases.

Side Effects
  • Automatic commit

Standards

  • SQL—ISO/ANSI SQL compliant.

  • Sybase—Sybase IQ does not support the use of REVOKE statements within the CREATE SCHEMA statement, and does not allow its use within Transact-SQL batches or procedures.

Permissions

Must have RESOURCE authority.

Related reference
CREATE TABLE Statement
CREATE VIEW Statement
GRANT Statement