> ## 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 VIEW The `ALTER VIEW` [statement](https://docs.cockroachlabs.com/docs/v24.2/sql-statements) applies a schema change to a [view](https://docs.cockroachlabs.com/docs/v24.2/views). > [!NOTE] > > The `ALTER VIEW` statement performs a schema change. For more information about how online schema changes work in CockroachDB, see [Online Schema Changes](https://docs.cockroachlabs.com/docs/v24.2/online-schema-changes). ## Required privileges * To change the schema of a view with `ALTER VIEW ... SET SCHEMA`, the user must have the `DROP` [privilege](https://docs.cockroachlabs.com/docs/v24.2/security-reference/authorization#managing-privileges) 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](https://docs.cockroachlabs.com/docs/v24.2/security-reference/authorization#managing-privileges) on the view and the `CREATE` privilege on the view's database. ## Syntax ![alter\_view syntax diagram](https://mintcdn.com/cockroachlabs/O8LdfYQ-dz7oGPVh/images/sql-diagrams/v24.2/alter_view.svg?fit=max\&auto=format\&n=O8LdfYQ-dz7oGPVh\&q=85\&s=1b92a2a90e63c362743e96397f3d4275) ## Parameters | Parameter | Description | | - | - | | `MATERIALIZED` | Rename a [materialized view](https://docs.cockroachlabs.com/docs/v24.2/views#materialized-views). | | `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](https://docs.cockroachlabs.com/docs/v24.2/keywords-and-identifiers#identifiers). 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`](https://docs.cockroachlabs.com/docs/v24.2/select-clause) 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](https://docs.cockroachlabs.com/docs/v24.2/sql-name-resolution#lookup-with-unqualified-names) 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](https://docs.cockroachlabs.com/docs/v24.2/create-schema): ```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](https://docs.cockroachlabs.com/docs/v24.2/views) * [`CREATE VIEW`](https://docs.cockroachlabs.com/docs/v24.2/create-view) * [`SHOW CREATE`](https://docs.cockroachlabs.com/docs/v24.2/show-create) * [`DROP VIEW`](https://docs.cockroachlabs.com/docs/v24.2/drop-view) * [Online Schema Changes](https://docs.cockroachlabs.com/docs/v24.2/online-schema-changes)