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

authorization_name – is the name of the current user in the database.
create_object_statement – is a create table or create view statement.
permission_statement – is a grant or revoke command.

Examples

Example 1 – Creates the newtitles, newauthors, newtitleauthors tables, the tit_auth_view view, and the corresponding permissions:

create schema authorization pogo
    create table newtitles (
        title_id tid not null,
        title varchar (30) not null)

    create table newauthors (
        au_id id not null,
        au_lname varchar (40) not null,
        au_fname varchar (20) not null)

    create table newtitleauthors (
        au_id id not null,
        title_id tid not null)

    create view tit_auth_view
    as
        select au_lname, au_fname
            from newtitles, newauthors,
                newtitleauthors
        where 
        newtitleauthors.au_id = newauthors.au_id
        and 
        newtitleauthors.title_id =
             newtitles.title_id

    grant select on tit_auth_view to public
    revoke select on tit_auth_view from churchy

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.

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.