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 ViewsIt 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 {ENCRYPTIONSCHEMABINDINGVIEW_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 ViewALTER VIEW dbo.contactview WITH ENCRYPTION AS SELECT CompanyName, ContactName, ContactTitle, Phone FROM dbo.Customers Go
Dropping ViewsYou 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. |