Altering and Dropping Views :: Chapter 27. Creating and Managing Views in SQL Server :: Part IV: Transact-SQL :: Microsoft SQL Server 2000 :: SQL

Altering and Dropping Views

If you want to change the definition of a view, you can do so using the ALTER VIEW statement, or by dropping and re-creating the view. Views can be dropped using the DROP VIEW statement.

Altering Views

It is better to alter a view to change its definition than to drop and re-create the view; when you alter the view, the permissions that have been assigned are retained, and dependent objects such as INSTEAD OF triggers and stored procedures aren't affected. The ALTER VIEW statement accepts the same parameters and options as CREATE VIEW. The ALTER VIEW syntax is as follows:

ALTER VIEW owner.view_name [(column[,n])] 
[WITH {ENCRYPTION|SCHEMABINDING|VIEW_METADATA}[,n]]
AS
select_statement
[WITH CHECK OPTION]

Since the ALTER VIEW statement re-creates the view definition, any options specified in the original view that you want to retain also must be specified in the ALTER VIEW statement. Alternatively, any options that you want to add can be specified when you alter the view. Listing 27.7 alters the contactview view to add the ENCRYPTION option.

Listing 27.7 Altering a View

ALTER VIEW dbo.contactview
WITH ENCRYPTION
AS
SELECT CompanyName, ContactName, ContactTitle, Phone
FROM  dbo.Customers
Go

NOTE

If you define a view with the SELECT * statement, and then subsequently add columns to the table, the new columns will not appear in the view. Use the ALTER VIEW command to redefine the view to get the new columns to appear.

In addition, if columns are dropped from the table, queries against a view defined with SELECT * will fail until the view is altered or dropped and re-created.

You can prevent these problems by creating the view with the WITH SCHEMABINDING option, which will not allow these types of changes to be made to the table until the view is dropped first.

Dropping Views

You can drop a view that is no longer needed with the DROP VIEW command, or you can select and delete it in Enterprise Manager. The following is an example of the DROP VIEW command to drop the view contactview:

DROP VIEW dbo.contactview

Bear in mind that if a dropped view must be re-created, you need to reassign all permissions. If any other views reference the dropped view, then users querying those views will receive an error message. Before dropping an object such as a view, run sp_depends 'viewname' or right-click the view in Enterprise Manager and select All Tasks, Display Dependencies to determine whether dependent objects exist.