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