CREATE SCHEMA (T-SQL)

Creates a schema that can be thought of as a conceptual object containing definitions of tables, views, and granted permissions.

Syntax

CREATE SCHEMA AUTHORIZATION owner
    [ <schema_element> [...n] ]

<schema_element> ::=
    {table_definition | view_definition | grant_statement}

Arguments

AUTHORIZATION owner

Specifies the ID of the schema object owner. This identifier must be a valid security account in the database.

table_definition

Specifies a CREATE TABLE statement that creates a table within the schema.

view_definition

Specifies a CREATE VIEW statement that creates a view within the schema.

grant_statement

Specifies a GRANT statement that grants permissions to a user or group of users.

Remarks

CREATE SCHEMA provides a way to create tables and views and to grant permissions on the objects, all within a single statement. If errors occur when creating any of the objects or granting any permissions specified in a CREATE SCHEMA statement, none of the objects is created.

The created objects do not have to appear in logical order, except for views that reference other views. For example, a GRANT statement can grant permission to an object before the object itself has been created, or a CREATE VIEW statement can appear before the CREATE TABLE statements creating the tables referenced by the view. Also, CREATE TABLE statements can declare foreign keys to tables that are specified later. The exception is that if the select from one view references another view, the referenced view must be specified before the view that references it.

Permissions

CREATE SCHEMA permissions default to members of the db_owner and db_ddladmin fixed database roles.

Examples

A. Grant access to objects before object creation

This example shows permissions 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))