Modifying and Renaming a View

After a view is defined, you can change its name or modify its definition without dropping and re-creating the view, thus losing the permissions associated with the view. When you rename a view, follow these guidelines:

• The view to be renamed must be in the current database.
• The new name must follow the rules for identifiers.
• You can rename only views that you own.
• The database owner can change the name of any user’s view.

Altering a view does not affect any dependent objects such as stored procedures or triggers unless the definition of the view changes in such a way that the dependent object is no longer valid. For example, a view authors_view in the pubs database is defined as:

CREATE VIEW authors_view
AS
    SELECT au_id FROM authors

The stored procedure authors_proc is defined as:

CREATE PROC authors_proc
AS
    SELECT au_id from authors_view

authors_view is modified to retrieve the column au_lname instead of au_id:

ALTER VIEW authors_view
AS
    SELECT au_lname FROM authors

authors_proc will now fail when executed because the column au_id no longer exists in the view.

You can also modify a view to encrypt its definition, or to ensure that all data modification statements executed against the view adhere to the criteria set within the SELECT statement defining the view. For more information, see Creating a View.

To modify a view

To rename a view

Note Renaming a view does not change the name of the view in the text of the view's definition. To change the name of the view in the definition, modify the view directly.