> ## 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 VIEW
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
{children}
;
};
The `ALTER VIEW` statement applies a schema change to a view.
The \`\` statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.
## Required privileges
* To change the schema of a view with `ALTER VIEW ... SET SCHEMA`, the user must have the `DROP` privilege on the view and the `CREATE` privilege on the new schema.
* To rename a view with `ALTER VIEW ... RENAME TO`, the user must have the `DROP` privilege on the view and the `CREATE` privilege on the view's database.
## Syntax
## Parameters
| Parameter | Description |
| --------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `MATERIALIZED` | Rename a materialized view. |
| `IF EXISTS` | Rename the view only if a view of `view_name` exists; if one does not exist, do not return an error. |
| `view_name` | The name of the view to rename. To find existing view names, use:
`SELECT * FROM information_schema.tables WHERE table_type = 'VIEW';` |
| `view_new_name` | The new name of the view. The name of the view must be unique to its database and follow these identifier rules. Name changes do not propagate to the table(s) using the view. |
| `schema_name` | The name of the new schema. |
| `role_spec` | The role to set as the owner of the view. |
## Known limitations
`ALTER VIEW` does not currently support:
* Changing the `SELECT` statement executed by a view. Instead, you must drop the existing view and create a new view.
* Renaming a view that other views depend on. This feature may be added in the future.
## Examples
### Rename a view
Suppose you create a new view that you want to rename:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE VIEW money_rides (id, revenue) AS SELECT id, revenue FROM rides WHERE revenue > 50;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
WITH x AS (SHOW TABLES) SELECT * FROM x WHERE type = 'view';
```
```
schema_name | table_name | type | owner | estimated_row_count | locality
--------------+-------------+------+-------+---------------------+-----------
public | money_rides | view | demo | 0 | NULL
(1 row)
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER VIEW money_rides RENAME TO expensive_rides;
```
```
RENAME VIEW
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
WITH x AS (SHOW TABLES) SELECT * FROM x WHERE type = 'view';
```
```
schema_name | table_name | type | owner | estimated_row_count | locality
--------------+-----------------+------+-------+---------------------+-----------
public | expensive_rides | view | demo | 0 | NULL
(1 row)
```
Note that `RENAME TO` only changes the name of the view; it cannot move the view to a different database or schema. To change a view's schema, [use the `SET SCHEMA` clause](#change-the-schema-of-a-view).
### Change the schema of a view
Suppose you want to add the `expensive_rides` view to a schema called `cockroach_labs`:
By default, unqualified views created in the database belong to the `public` schema:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW CREATE public.expensive_rides;
```
```
table_name | create_statement
-------------------------+-------------------------------------------------------------------------------------------------------------------
public.expensive_rides | CREATE VIEW public.expensive_rides (id, revenue) AS SELECT id, revenue FROM movr.public.rides WHERE revenue > 50
(1 row)
```
If the new schema does not already exist, create it:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE SCHEMA IF NOT EXISTS cockroach_labs;
```
Then, change the view's schema:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER VIEW expensive_rides SET SCHEMA cockroach_labs;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW CREATE public.expensive_rides;
```
```
ERROR: relation "public.expensive_rides" does not exist
SQLSTATE: 42P01
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW CREATE cockroach_labs.expensive_rides;
```
```
table_name | create_statement
---------------------------------+---------------------------------------------------------------------------------------------------------------------------
cockroach_labs.expensive_rides | CREATE VIEW cockroach_labs.expensive_rides (id, revenue) AS SELECT id, revenue FROM movr.public.rides WHERE revenue > 50
(1 row)
```
## See also
* Views
* `CREATE VIEW`
* `SHOW CREATE`
* `DROP VIEW`
* Online Schema Changes