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

# ALTER SCHEMA

The `ALTER SCHEMA` [statement](/docs/stable/sql-statements.md) modifies a user-defined [schema](/docs/stable/sql-name-resolution.md#naming-hierarchy). CockroachDB currently supports changing the name of the schema and the owner of the schema.

> [!NOTE]
>
> The `ALTER SCHEMA` statement performs a schema change. For more information about how online schema changes work in CockroachDB, see [Online Schema Changes](/docs/stable/online-schema-changes.md).

## Syntax

![alter\_schema syntax diagram](https://mintcdn.com/cockroachlabs/VQ_w3c6MHYdiS7Tn/images/sql-diagrams/common/alter_schema.svg?fit=max\&auto=format\&n=VQ_w3c6MHYdiS7Tn\&q=85\&s=7f379da2e30daafa679b41994342bb5e)

### Parameters

| Parameter | Description |
| - | - |
| `name` `name.name` | The name of the schema to alter, or the name of the database containing the schema and the schema name, separated by a "`.`". |
| `schema_new_name` | The name of the new schema. The new schema name must be unique within the database and follow these [identifier rules](/docs/stable/keywords-and-identifiers.md#identifiers). |
| `role_spec` | The role to set as the owner of the schema. |

## Required privileges

* To rename a schema, the user must be the owner of the schema.
* To change the owner of a schema, the user must be the current owner of the schema and a member of the new owner [role](/docs/stable/security-reference/authorization.md#roles). The new owner role must also have the `CREATE` [privilege](/docs/stable/security-reference/authorization.md#managing-privileges) on the database to which the schema belongs.

## Examples

#### Setup

To follow along, run [`cockroach demo`](/docs/stable/cockroach-demo) to start a temporary, in-memory cluster with the [`movr`](/docs/stable/movr) sample dataset preloaded:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach demo
```

### Rename a schema

> [!NOTE]
>
> You cannot rename a schema if a [table](/docs/stable/create-table.md) in the schema is used by a [view](/docs/stable/create-view.md) or [user-defined function](/docs/stable/user-defined-functions.md).

Suppose that you access the [SQL shell](/docs/stable/cockroach-sql.md) as user `root`, and [create a new user](/docs/stable/create-user.md) `max` and [a schema](/docs/stable/create-schema.md) `org_one` with `max` as the owner:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE USER max;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE SCHEMA org_one AUTHORIZATION max;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW SCHEMAS;
```

```
     schema_name
----------------------
  crdb_internal
  information_schema
  org_one
  pg_catalog
  pg_extension
  public
(6 rows)
```

Now, suppose you want to rename the schema:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER SCHEMA org_one RENAME TO org_two;
```

```
ERROR: must be owner of schema "org_one"
SQLSTATE: 42501
```

Because you are executing the `ALTER SCHEMA` command as a non-owner of the schema (i.e., `root`), CockroachDB returns an error.

[Drop the schema](/docs/stable/drop-schema.md) and create it again, this time with `root` as the owner.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
DROP SCHEMA org_one;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE SCHEMA org_one;
```

To verify that the owner is now `root`, query the `pg_catalog.pg_namespace` and `pg_catalog.pg_users` tables:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT
  nspname, usename
FROM
  pg_catalog.pg_namespace
  LEFT JOIN pg_catalog.pg_user ON pg_namespace.nspowner = pg_user.usesysid
WHERE
  nspname LIKE 'org_one';
```

```
  nspname | usename
----------+----------
  org_one | root
(1 row)
```

As its owner, you can rename the schema:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER SCHEMA org_one RENAME TO org_two;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW SCHEMAS;
```

```
     schema_name
----------------------
  crdb_internal
  information_schema
  org_two
  pg_catalog
  pg_extension
  public
(6 rows)
```

### Change the owner of a schema

Suppose that you access the [SQL shell](/docs/stable/cockroach-sql.md) as user `root`, and [create a new schema](/docs/stable/create-schema.md) named `org_one`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE SCHEMA org_one;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW SCHEMAS;
```

```
     schema_name
----------------------
  crdb_internal
  information_schema
  org_one
  pg_catalog
  pg_extension
  public
(6 rows)
```

Now, suppose that you want to change the owner of the schema `org_one` to an existing user named `max`. To change the owner of a schema, the current owner must belong to the role of the new owner (in this case, `max`), and the new owner must have `CREATE` privileges on the database.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
GRANT max TO root;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
GRANT CREATE ON DATABASE defaultdb TO max;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER SCHEMA org_one OWNER TO max;
```

To verify that the owner is now `max`, query the `pg_catalog.pg_namespace` and `pg_catalog.pg_users` tables:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT
  nspname, usename
FROM
  pg_catalog.pg_namespace
  LEFT JOIN pg_catalog.pg_user ON pg_namespace.nspowner = pg_user.usesysid
WHERE
  nspname LIKE 'org_one';
```

```
  nspname | usename
----------+----------
  org_one | max
(1 row)
```

## See also

* [`CREATE SCHEMA`](/docs/stable/create-schema)
* [`SHOW SCHEMAS`](/docs/stable/show-schemas)
* [`DROP SCHEMA`](/docs/stable/drop-schema)
* [Online Schema Changes](/docs/stable/online-schema-changes.md)