create schema

Creates a new collection of tables, views, and permissions for a database user.

Syntax

create schema authorization authorization_name
	create_object_statement 
		[create_object_statement ...]
	[permission_statement ...]

Parameters

Examples

Usage

  • Schemas can be created only in the current database.

  • The authorization_name, also called the schema authorization identifier, must be the name of the current user.

  • The user must have the correct command permissions (create table and create view). If the user creates a view on tables owned by another database user, permissions on the view are checked when a user attempts to access data through the view, not when the view is created.

  • The create schema command is terminated by:
    • The regular command terminator (“go” is the default in isql).

    • Any statement other than create table, create view, grant, or revoke.

  • If any of the statements within a create schema statement fail, the entire command is rolled back as a unit, and none of the commands take effect.

  • create schema adds information about tables, views, and permissions to the system tables. Use the appropriate drop command (drop table or drop view) to drop objects created with create schema. You cannot change permissions granted or revoked in a schema with the standard grant and revoke commands outside the schema creation statement.

  • Clusters only – you cannot include a referential integrity constraint that references a column on a local temporary database unless it is from a table on the same local temporary database. create schema fails when it attempts to create a reference to a column on a local temporary database from a table in another database.

See also isql in the Utility Guide.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

create schema can be executed by any user of a database. The user must have permission to create the objects specified in the schema; that is, create table and create view permission.

Related reference
create table
create view
grant
revoke
drop table
drop view