O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required