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 AdventureWorks2008R2 database is defined as:

CREATE VIEW dbo.employees_view
AS
   SELECT BusinessEntityID FROM HumanResources.Employee;

The stored procedure employees_proc is defined as:

CREATE PROC dbo.employees_proc
AS
   SELECT EmployeeID from employees_view;

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

ALTER VIEW dbo.employees_view
AS
   SELECT LastName FROM Person.Person AS p
   JOIN HumanResources.Employee AS e 
    ON p.BusinessEntityID = e.BusinessEntityID;

employees_proc now fails when executed because the column BusinessEntityID 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.

See Also

Concepts