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