create schema

Description

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

Syntax

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

Parameters

authorization_name

must be 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

Standards

SQL92 – 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/or create view permission.

See also

Commands create table, create view, grant, revoke

Utilities isql