ALTER VIEW Statement

Replaces a view definition with a modified version.

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 }

Usage

AS—Purpose and syntax identical to CREATE VIEW statement. See CREATE VIEW Statement.

WITH CHECK OPTION —Purpose and syntax identical to CREATE VIEW statement. See CREATE VIEW Statement.

SET HIDDEN—Obfuscate the definition of the view and cause the view to become hidden from view, for example in Sybase Central. Explicit references to the view still work.

Warning!  The SET HIDDEN operation is irreversible.

RECOMPILE—Recreate the column definitions for the view. Identical in functionality to the ENABLE clause, except you can use it on a view that is not disabled.

DISABLE—Disable the view from use by the database server.

ENABLE—Enable a disabled view, which causes the database server to recreate the column definitions for the view. Before you enable a view, you must enable any views on which it depends.

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.

Syntax 1—Alter the structure of the view. Unlike altering tables, where your change might be limited to individual columns, 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. For a description of the parameters used to define the structure of a view, see CREATE VIEW Statement.

Syntax 2—Change attributes for the view, such as whether the view definition is hidden.

When you use SET HIDDEN, you can unload and reload the view into other databases. Debugging using the debugger does not show the view definition, nor is it available through procedure profiling. If you need to change the definition of a hidden view, you must drop the view and create it again using the CREATE VIEW statement.

When you use the DISABLE clause, the view is no longer available for use by the database server to answer queries. Disabling a view is similar to dropping one, except that the view definition remains in the database. Disabling a view also disables any dependent views. Therefore, the DISABLE clause requires exclusive access, not only to the view being disabled, but to any dependent views, which are also disabled.

See also System Administration Guide: Volume 2 > Using Procedures and Batches > Hiding the Contents of Procedures, Functions, and Views, Reference: Building Blocks, Tables, and Procedures > System Procedures > System Stored Procedures > sa_dependent_views Procedure.

For detailed information on how the database server handles view dependencies, see SQL Anywhere Server - SQL Usage > Working with database objects > Working with views > View Dependencies.
Note: This reference points to SQL Anywhere documentation.
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

  • SQL—Vendor extension to ISO/ANSI SQL grammar.

  • Sybase—Not supported by Adaptive Server Enterprise.

Permissions

Must be owner of the view or have DBA authority.

Related tasks
Identifying and Fixing Invalid Dependent Views
Related reference
CREATE VIEW Statement
DROP Statement