> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ocient.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Schemas

export const Ocient = "Ocient®";

This group of DDL SQL statements allows database administrators to manage schema design. Database administrators can create, modify, and drop schemas. You can view information about the schema using the `sys.schemas` system catalog table.

For information on other database components, see the pages on [Databases](/databases), [Tables](/tables), [Views](/views), and [Indexes](/indexes).

## Implicit and Explicit Schemas

The {Ocient} System creates implicit schema objects when you execute the `CREATE` 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.

<Info>
  After promotion, you cannot demote an explicit schema back to an implicit one.
</Info>

## 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](/data-control-language-dcl-statement-reference).

**Syntax**

```sql SQL theme={null}
CREATE [ OR REPLACE ] SCHEMA [ IF NOT EXISTS ] schema_name
```

| **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. <br /><br />`sys`, `sysgdc`, `syslookup`, and `information_schema` are reserved schema names and can neither be created nor dropped. |

**Example**

Define the `sandbox` schema.

```sql SQL theme={null}
CREATE SCHEMA sandbox;
```

## 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:

* `VIEW` and `ALTER` privilege on the current schema
* `CREATE SCHEMA` privilege on the database

**Syntax**

```sql SQL theme={null}
ALTER SCHEMA [ IF EXISTS ] old_schema_name RENAME TO new_schema_name
```

| **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. |

**Example**

Rename an existing schema named `sandbox` to `ocient`.

```sql SQL theme={null}
ALTER SCHEMA sandbox RENAME TO ocient;
```

## 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 SQL theme={null}
DROP SCHEMA [ IF EXISTS ] schema_name [, ...] [ CASCADE ]
```

| **Parameter** | **Type** | **Description**                                                                                                                                                                                                                                                                               |
| ------------- | -------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `schema_name` | string   | An identifier for the schema to remove.<br /><br />You can drop multiple schemas by specifying additional schema names and separating each with commas. <br /> <br />`sys`, `sysgdc`, `syslookup`, and `information_schema` are reserved schema names and can neither be created nor dropped. |

**Example**

Remove an existing schema named `ocient`.

```sql SQL theme={null}
DROP SCHEMA ocient;
```

## Related Links

[Core Elements of an Ocient System](/core-elements-of-an-ocient-system)

[General SQL Syntax](/general-sql-syntax)

[Database Password Security Settings](/database-password-security-settings)

[System Catalog](/system-catalog)