Creates a new collection of tables, views, and permissions for a database user.
create schema authorization authorization_name create_oject_statement [create_object_statement ... ] [permission_statement ... ]
must be the name of the current user in the database.
is a create table or create view statement.
is a grant or revoke command.
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
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/or 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).
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. Permissions granted or revoked in a schema can be changed with the standard grant and revoke commands outside the schema creation statement.
SQL92 – Compliance level: Transact-SQL extension.
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.
Commands create table, create view, grant, revoke