ALTER VIEW Statement

Replaces a view definition with a modified version.

Quick Links:

Go to Parameters

Go to Usage

Go to Standards

Go to Permissions

Syntax

Syntax 1 – Alter the structure of the view

ALTER VIEW
   … [ owner.]view-name [ ( column-name [ , … ] ) ]
   … AS select-statement
   … [ WITH CHECK OPTION ]

Syntax 2 – Change attributes for the view

ALTER VIEW
   … [ owner.]view-name 
   … { SET HIDDEN | RECOMPILE | DISABLE | ENABLE }

Parameters

(back to top)

Usage

(back to top)

When you alter a view, existing permissions on the view are maintained and do not require reassignment. Instead of using the ALTER VIEW statement, you could also drop the view and recreate it using DROP VIEW and CREATE VIEW, respectively. If you do this, view permissions must be reassigned.

After completing the view alteration using Syntax 1, the database server recompiles the view. Depending on the type of change you made, if there are dependent views, the database server attempts to recompile them. If you made changes that impact a dependent view, that view may become invalid, requiring you to alter the definition for the dependent view.

Warning!   If the SELECT statement defining the view contains an asterisk (*), the number of the columns in the view could change if columns were added or deleted from the underlying tables. The names and data types of the view columns could also change.

Altering the structure of a view requires that you replace the entire view definition with a new definition, much as you would when creating the view using the CREATE VIEW statement.

Side effects:
  • Automatic commit
  • All procedures and triggers are unloaded from memory, so that any procedure or trigger that references the view reflects the new view definition. The unloading and loading of procedures and triggers can have a performance impact if you regularly alter views.

Standards

(back to top)

  • SQL—Vendor extension to ISO/ANSI SQL grammar.
  • SAP Sybase Database product—Not supported by Adaptive Server.

Permissions

(back to top)

RECOMPILE  or ENABLE clause – For view requires one of:
  • ALTER ANY VIEW system privilege.
  • ALTER ANY OBJECT system privilege.
  • You own the view.
  • Also require one of:
    • SELECT ANY TABLE system privilege.
    • SELECT privilege on the underlying tables of the view.
For materialized view requires one of:
  • ALTER ANY MATERIALNESS VIEW system privilege.
  • ALTER ANY OBJECT system privilege.
  • You own the materialized view.
  • Also require one of:
    • SELECT ANY TABLE system privilege.
    • SELECT privilege on the underlying tables of the materialized view.
DISABLE clause – For view requires one of:
  • ALTER ANY VIEW system privilege.
  • ALTER ANY OBJECT system privilege.
  • You own the view.
For materialized view requires one of:
  • ALTER ANY MATERIALIZED VIEW system privilege.
  • ALTER ANY OBJECT system privilege.
  • You own the materialized view.
All other clauses require one of:
  • ALTER ANY OBJECT system privilege.
  • You own the view.
Related tasks
Identifying and Fixing Invalid Dependent Views
Related reference
CREATE VIEW Statement
DROP Statement