SQL in a Nutshell

Name

ALTER VIEW

Synopsis

While there is currently no SQL99 standard for the ALTER VIEW , it is important to note that this command behaves differently in each major vendor application that supports it. Oracle uses this command to recompile a view; Microsoft SQL Server uses this command to allow modifications to a view without also updating any dependent stored procedures, triggers, or permissions.

Vendor	Command
SQL Server	Supported, with variations
MySQL	Not supported
Oracle	Supported, with variations
PostgreSQL	Not supported

SQL99 Syntax and Description

Currently, there is no SQL99 standard for this command.

Microsoft SQL Server Syntax and Variations

ALTER VIEW view_name [(column [,...n])]
[WITH {ENCRYPTION | SCHEMABINDING | VIEW_METADATA]
AS
select_statement 
[WITH CHECK OPTION]

As with the CREATE VIEW statement, ALTER VIEW allows a programmer to specify the column aliases that the view uses to name the columns, as well as the entire SELECT statement that is the core component of the view.

The other clauses of the ALTER VIEW statement are described under the CREATE VIEW statement.

Microsoft SQL Server can maintain the column permissions only if the column names remain the same after the command has been executed. The ENCRYPTION keyword allows the encryption of the views code within the syscomments system table in SQL Server. The keywords CHECK OPTION force all data modifications executed against the view to pass the criteria of its defining select_statement. If the view previously ...