Creates a new collection of tables, views, and permissions for a database user.
create schema authorization authorization_name create_object_statement [create_object_statement ...] [permission_statement ...]
is the name of the current user in the database.
is a create table or create view statement.
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 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.
ANSI SQL – 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 create view permission.
Commands create table, create view, grant, revoke
