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

# ALTER SCHEMA

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

The `ALTER SCHEMA` <InternalLink path="sql-statements">statement</InternalLink> modifies a user-defined <InternalLink path="sql-name-resolution#naming-hierarchy">schema</InternalLink>. 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 <InternalLink path="online-schema-changes">Online Schema Changes</InternalLink>.
</Note>

## Syntax

<img src="https://mintcdn.com/cockroachlabs/WFQ6LFOxKnUpgIkd/images/sql-diagrams/v24.1/alter_schema.svg?fit=max&auto=format&n=WFQ6LFOxKnUpgIkd&q=85&s=bbb5cd79506d35e9a7aee348eba0119d" alt="alter_schema syntax diagram" style={{maxWidth: "100%", overflowX: "auto"}} width="447" height="179" data-path="images/sql-diagrams/v24.1/alter_schema.svg" />

### 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 <InternalLink path="keywords-and-identifiers#identifiers">identifier rules</InternalLink>. |
| `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 <InternalLink path="security-reference/authorization#roles">role</InternalLink>. The new owner role must also have the `CREATE` <InternalLink path="security-reference/authorization#managing-privileges">privilege</InternalLink> on the database to which the schema belongs.

## Examples

#### Setup

To follow along, run <InternalLink path="cockroach-demo">`cockroach demo`</InternalLink> to start a temporary, in-memory cluster with the <InternalLink path="movr">`movr`</InternalLink> 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 <InternalLink path="create-table">table</InternalLink> in the schema is used by a <InternalLink path="create-view">view</InternalLink> or <InternalLink path="user-defined-functions">user-defined function</InternalLink>.
</Note>

Suppose that you access the <InternalLink path="cockroach-sql">SQL shell</InternalLink> as user `root`, and <InternalLink path="create-user">create a new user</InternalLink> `max` and <InternalLink path="create-schema">a schema</InternalLink> `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;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
     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;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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.

<InternalLink path="drop-schema">Drop the schema</InternalLink> 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';
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  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;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
     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 <InternalLink path="cockroach-sql">SQL shell</InternalLink> as user `root`, and <InternalLink path="create-schema">create a new schema</InternalLink> 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;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
     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';
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  nspname | usename
----------+----------
  org_one | max
(1 row)
```

## See also

* <InternalLink path="create-schema">`CREATE SCHEMA`</InternalLink>
* <InternalLink path="show-schemas">`SHOW SCHEMAS`</InternalLink>
* <InternalLink path="drop-schema">`DROP SCHEMA`</InternalLink>
* <InternalLink path="online-schema-changes">Online Schema Changes</InternalLink>