sys.schemas system catalog table.
For information on other database components, see the pages on Databases, Tables, Views, and Indexes.
Implicit and Explicit Schemas
The System creates implicit schema objects when you execute theCREATE or ALTER SQL statements on a table, view, or machine learning model in the database, and there is no destination schema for those objects. For example, in the ALTER TABLE schema1.table1 RENAME to schema2.table1 statement, if schema2 does not exist in the database, the system creates schema2 as an implicit schema object.
The system creates explicit schema objects when you execute a CREATE SCHEMA SQL statement or promote an implicit schema using the ALTER SCHEMA SQL statements or the GRANT SQL statement on the schema.
To execute SQL statements on objects or view the metadata of objects in an explicit schema, you must have the VIEW SCHEMA privilege and the basic required privileges for the statement. For an implicit schema, you do not need any additional privileges.
To create an object, if the schema is implicit, then you need the CREATE object ON DATABASE privilege, where object can be a table, view, or machine learning model. If the schema is explict, then you need the CREATE object ON SCHEMA privilege.
You can check the disposition of the schema using the is_implicit column in the sys.schemas system catalog table.
After promotion, you cannot demote an explicit schema back to an implicit one.
CREATE SCHEMA
CREATE SCHEMA defines an explicit schema. To use the optional OR REPLACE SQL statement, the schema must not contain other objects.
Required Privileges
Users with the CREATE SCHEMA privilege on the database can create an explicit schema.
After you create an explicit schema, ensure to specify the privileges for each user. For details, see Data Control Language (DCL) Statement Reference.
Syntax
SQL
| Parameter | Type | Description |
|---|---|---|
schema_name | string | An identifier for the schema to create. The name must be distinct from any existing schema in the database unless you specify the REPLACE keyword. sys, sysgdc, syslookup, and information_schema are reserved schema names and can neither be created nor dropped. |
sandbox schema.
SQL
ALTER SCHEMA RENAME
ALTER SCHEMA RENAME renames an existing schema. If you use this statement with an implicit schema, the system promotes the implicit schema to an explicit schema.
Required Privileges
To rename a schema, you must have the VIEW and ALTER privileges for the schema.
The Ocient System requires these privileges if this statement includes a change to the schema:
VIEWandALTERprivilege on the current schemaCREATE SCHEMAprivilege on the database
SQL
| Parameter | Type | Description |
|---|---|---|
old_schema_name | string | The old identifier of the schema for rename. |
new_schema_name | string | The new identifier of the schema for rename. The name must be distinct from the name of any existing schema in the system. |
sandbox to ocient.
SQL
DROP SCHEMA
DROP SCHEMA removes an existing schema. The Ocient System automatically removes an implicit schema when you remove all its contents. However, an explicit schema persists even without any objects.
If the existing schema contains objects and you do not specify the CASCADE keyword, the system cannot remove this schema. Otherwise, the system removes all objects inside the existing schema.
Required Privileges
If the schema is explicit, you must have the DROP SCHEMA privilege. Otherwise, if the schema is implicit, the schema must not contain any objects, or if you specify the CASCADE keyword, you must have the VIEW and DROP privileges for all objects in the schema to remove it.
Syntax
SQL
| Parameter | Type | Description |
|---|---|---|
schema_name | string | An identifier for the schema to remove. You can drop multiple schemas by specifying additional schema names and separating each with commas. sys, sysgdc, syslookup, and information_schema are reserved schema names and can neither be created nor dropped. |
ocient.
SQL