The CREATE SCHEMA statement creates a schema. A schema is a collection of tables and views along with their associated privileges.
The userid must be the user ID of the current connection. You cannot create a schema for another user.
If any statement contained in the CREATE SCHEMA statement fails, the entire CREATE SCHEMA statement is rolled back.
The CREATE SCHEMA statement is a way of collecting together 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 privileges, you must use a REVOKE statement for each privilege granted.
The 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 privileges are granted
Although you can create more than one schema for a user, doing so is not recommended.
The system privileges required depend on the operation specified in the CREATE SCHEMA statement you define. For information
about the required system privileges required, see the System privileges sections for applicable statements (CREATE TABLE,
CREATE VIEW, and GRANT).
CREATE SCHEMA is a core feature of the SQL/2008 standard. The ability to create multiple schemas for a single user is
SQL/2008 optional language feature F171. SQL Anywhere does not support the use of REVOKE statements within the CREATE SCHEMA
statement, and does not allow their use within Transact-SQL batches or procedures.
Supported by Adaptive Server Enterprise, which supports GRANT and REVOKE statements within the CREATE SCHEMA statement.
The following CREATE SCHEMA statement creates a schema consisting of two tables. The statement must be executed by the user
ID sample_user, who must have the CREATE TABLE system privilege. If the statement creating table t2 fails, neither table is
The statement delimiter in the following CREATE SCHEMA statement is placed after the first CREATE TABLE statement. As the
statement delimiter marks the end of the CREATE SCHEMA statement, the example is interpreted as a two statement batch by the
database server. If the statement creating table t2 fails, the table t1 is still created.