Views - Ocient Documentation

This group of DDL SQL statements allows database administrators to manage views. Database administrators can create, modify, drop, and export views. You can view information about views using the sys.views system catalog table. For information on database components, see the pages on Databases, Schemas, Tables, and Indexes.

CREATE VIEW

CREATE VIEW creates a new view in the current database or replaces an existing view. For view creation, the name of the view must be distinct from the name of any existing views in the database. To create a view, you must have both the CREATE VIEW privilege for the current database and the SELECT privilege on all directly referenced tables and views in the query. You must have DROP privileges on the view. You also must have CREATE VIEW privileges on the database. Syntax

SQL

CREATE [ OR REPLACE ] VIEW [ IF NOT EXISTS ] view_name AS query

Parameter	Type	Description
`view_name`	string	A distinct identifier used to name the view.
`query`	string	A `SELECT` query that defines the data from a table used to create the view.

Example This example creates an existing view in the current database and schema named option_trades.

SQL

CREATE VIEW option_trades
    AS SELECT * FROM trades WHERE type = 'OPTIONS';

DROP VIEW

DROP VIEW removes one or more existing views in the current database. To remove a view, you must have the DROP VIEW privilege for the view. Syntax

SQL

DROP VIEW [ IF EXISTS ] view_name [, ...]

Parameter	Type	Description
`view_name`	string	The identifier of the view to drop. You can drop multiple views by specifying additional view names and separating each with commas.

Example This example drops an existing view in the current database and schema named star_employees.

SQL

DROP VIEW star_employees;

This example drops two views named star_employees and bad_employees.

SQL

DROP VIEW star_employees, bad_employees;

ALTER VIEW RENAME

ALTER VIEW RENAME renames an existing view. Required Privileges To rename a view, you must have the ALTER VIEW privilege for the view. The System requires these privileges if this statement includes a change to the schema:

VIEW privilege on the current schema of the view
VIEW VIEW and CREATE VIEW privileges on the target schema (if the schema already exists)
CREATE VIEW privilege on the database (if the schema does not exist)

Syntax

SQL

ALTER VIEW [ IF EXISTS ] old_view_name RENAME TO new_view_name

Parameter	Type	Description
`old_view_name`	string	The identifier of the view to rename.
`new_view_name`	string	The new name for the specified view.

Examples This example renames an existing view in the current database and schema named star_employees to star_mid_west_employees.

SQL

ALTER VIEW star_employees RENAME TO star_mid_west_employees;

This example renames an existing view in the current database named us.star_employees to us.star_mid_west_employees.

SQL

ALTER VIEW us.star_employees RENAME TO us.star_mid_west_employees;

ALTER VIEW AS

ALTER VIEW AS modifies an inner query of the existing view. To modify the query for an existing view, you must be a system-level user or have the ALTER VIEW privilege for the view. You must also have the SELECT privilege on all referenced tables and views in the new query. Syntax

SQL

ALTER VIEW [ IF EXISTS ] view_name AS query

Parameter	Type	Description
`view_name`	string	The identifier for the view to alter.
`query`	string	A `SELECT` query that defines the new data from a table used to alter the view.

Example This example alters a view in the current database and schema named star_employees.

SQL

ALTER VIEW star_employees AS SELECT * FROM sys.tables;

EXPORT VIEW

EXPORT VIEW shows the CREATE VIEW statement for an existing view in the current database. To export a view, the logged-in user must be a system-level user or have the READ VIEW right for the view. Syntax

SQL

EXPORT VIEW view_name

Parameter	Type	Description
`view_name`	string	The identifier for the view to export.

Example This example exports an existing view in the current database and schema named students.

SQL

EXPORT VIEW students;

Core Elements of an Ocient System Data Query Language (DQL) Statement Reference Database Password Security Settings System Catalog

​CREATE VIEW

​DROP VIEW

​ALTER VIEW RENAME

​ALTER VIEW AS

​EXPORT VIEW

​Related Links

CREATE VIEW

DROP VIEW

ALTER VIEW RENAME

ALTER VIEW AS

EXPORT VIEW

Related Links