Export (0) Print
Expand All

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. Dropping and re-creating a view causes the permissions associated with the view to be lost. When you rename a view, consider the following 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 have permission to change.
  • The database owner can change the name of any user's view.

Modifying 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 employees_view in the AdventureWorks database is defined as:

CREATE VIEW employees_view
AS
   SELECT EmployeeID FROM HumanResources.Employee

The stored procedure employees_proc is defined as:

CREATE PROC employees_proc
AS
   SELECT EmployeeID from employees_view

employees_view is modified to retrieve the column LastName instead of EmployeeID:

ALTER VIEW employees_view
AS
   SELECT LastName FROM Person.Contact c
   JOIN HumanResources.Employee e ON c.ContactID = e.ContactID

employees_proc now fails when executed because the column EmployeeID no longer exists in the view.

You can also modify a view to encrypt its definition, or to make sure that all data modification statements executed against the view follow the criteria set within the SELECT statement that is defining the view. For more information, see Designing and Implementing Views.

To modify a view

To rename a view

ms178076.note(en-US,SQL.90).gifNote:
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.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft