CREATE SCHEMA Statement (version 6.5)

Creates a schema that can be thought of as a conceptual container object, which is the definition of the database without any data in it.

Syntax

CREATE SCHEMA
    [AUTHORIZATION owner]
    [schema_element [schema_element2[...schema_elementn]]]

where

AUTHORIZATION owner

Specifies the ID of the schema object owner. This identifier must begin with a letter and can have as many as 30 characters. Hyphens are not supported.

schema_element = {table_definition | view_definition | grant_statement}

table_definition = CREATE TABLE statement

Creates a table within this defined schema. For more information about the CREATE TABLE statement, see the Microsoft SQL Server Transact-SQL Reference.

view_definition = CREATE VIEW statement

Creates a view for this schema. For more information about the CREATE VIEW statement, see the Microsoft SQL Server Transact-SQL Reference.

grant_statement = GRANT statement

Grants permissions to a user or group of users. For more information, see GRANT statement later in this document.

Remarks

Often database developers find it useful to keep a script or procedure, which creates the database schema for creating a copy of the database for testing or installation. Database administrators can set up a database and use the CREATE SCHEMA statement to set up all of the objects and permissions in a single batch.

CREATE SCHEMA provides a way to create database objects such as tables and views and to grant privileges on the objects, all within a single batch. The created objects do not need to appear in logical order. For example, a GRANT statement can grant permission to an object before the object itself has been created.

In another example, a CREATE VIEW statement can appear before the CREATE TABLE statement is created as long as all necessary objects are in the same CREATE SCHEMA statement. Also, the CREATE TABLE statement can declare foreign keys to tables that have not yet been created. Because objects are created at the same time, it is possible to create two tables that have mutually dependent foreign keys.

This statement complies with ANSI SQL standards. There are other ways to achieve the same results (DDL in transaction or add foreign key after creating both tables), and there are no restrictions to the objects created with CREATE SCHEMA other than that they are created under the current database user. In SQL Server, using the owner qualifier on database objects or on a separate database is usually preferable to using the CREATE SCHEMA statement.

ANSI-standards require that an AUTHORIZATION owner be included in the CREATE SCHEMA statement. Because a user must be signed on under a particular username in order to issue statements, the AUTHORIZATION owner is optional.

The tables and view can be specified in any order. For example, a view can appear before the table on which it is defined.

Note Errors from other statements in the CREATE SCHEMA statement, such as REVOKE or ALTER TABLE, will be ignored and not return error messages.

Examples

A.    Grant Access to Objects Before Object Creation

This example shows permission being granted before the objects have been created.

CREATE SCHEMA AUTHORIZATION ross 
GRANT SELECT on v1 TO public
CREATE VIEW v1(c1) AS SELECT c1 from t1
CREATE TABLE t1(c1 int)

B.    Create Mutually Dependent Foreign Key Constraints

This example creates mutually dependent foreign key constraints. Other methods would take several steps to accomplish what is enabled by this CREATE SCHEMA example.

CREATE SCHEMA AUTHORIZATION ross
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT REFERENCES t2(c1))
CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT REFERENCES t1(c1))