CREATE SCHEMA statement

Use the CREATE SCHEMA statement to issue a block of data definition language (DDL) and GRANT statements as a unit.

Use this statement with DB-Access.

Syntax

Read syntax diagramSkip visual syntax diagram
>>-CREATE SCHEMA AUTHORIZATION--user---------------------------->

   .---------------------------------------------------------.   
   V                              (1)                        |   
>----+-| CREATE TABLE Statement |--------------------------+-+-->
     |                           (2)                       |     
     +-| CREATE VIEW Statement |---------------------------+     
     |                     (3)                             |     
     +-| GRANT Statement |---------------------------------+     
     |  (4)                             (5)                |     
     '-----+-| CREATE INDEX Statement |------------------+-'     
           |                              (6)            |       
           +-| CREATE SYNONYM Statement |----------------+       
           |                              (7)            |       
           +-| CREATE TRIGGER Statement |----------------+       
           |                                 (8)         |       
           '-+-| CREATE SEQUENCE Statement |-----------+-'       
             |                               (9)       |         
             +-| CREATE ROW TYPE Statement |-----------+         
             |                                  (10)   |         
             +-| CREATE OPAQUE TYPE Statement |--------+         
             |                                    (11) |         
             +-| CREATE DISTINCT TYPE Statement |------+         
             |                           (12)          |         
             '-| CREATE CAST Statement |---------------'         

>--+---+-------------------------------------------------------><
   '-;-'   

Notes:
  1. See CREATE TABLE statement
  2. See CREATE VIEW statement
  3. See GRANT statement
  4. Informix® extension
  5. See CREATE INDEX statement
  6. See CREATE SYNONYM statement
  7. See CREATE TRIGGER statement
  8. See CREATE SEQUENCE statement
  9. See CREATE ROW TYPE statement
  10. See CREATE OPAQUE TYPE statement
  11. See CREATE DISTINCT TYPE statement
  12. See CREATE CAST statement
Element Description Restrictions Syntax
user User who owns the database objects that this statement creates If you have DBA privileges, you can specify the name of any user. Otherwise, you must have the Resource privilege, and you must specify your own user name. Owner name

Usage

The CREATE SCHEMA statement allows the DBA to specify an owner for all database objects that the CREATE SCHEMA statement creates. You cannot issue CREATE SCHEMA until you have created the database that stores the objects.

Users with the Resource privilege can create a schema for themselves. In this case, user must be the name of the person with the Resource privilege who is running the CREATE SCHEMA statement. Anyone with the DBA privilege can also create a schema for someone else. In this case, user can specify a user other than the person who is running the CREATE SCHEMA statement.

You can put CREATE and GRANT statements in any logical order, as the following example shows. Statements are considered part of the CREATE SCHEMA statement until a semicolon ( ; ) or an end-of-file symbol is reached.
CREATE SCHEMA AUTHORIZATION sarah
   CREATE TABLE mytable (mytime DATE, mytext TEXT)
   GRANT SELECT, UPDATE, DELETE ON mytable TO rick
   CREATE VIEW myview AS
      SELECT * FROM mytable WHERE mytime > '12/31/2004'
   CREATE INDEX idxtime ON mytable (mytime); 

Copyright© 2019 HCL Technologies Limited