Altering and Removing Views

The Transact-SQL language supports the nonstandard ALTER VIEW statement, which is used to modify the definition of the view query. The syntax of ALTER VIEW is analogous to that of the CREATE VIEW statement.

You can use the ALTER VIEW statement to avoid reassigning existing privileges for the view. Also, altering an existing view using this statement does not affect database objects that depend upon the view. Otherwise, if you use the DROP VIEW and CREATE VIEW statements to remove and re-create a view, any database object that uses the view will not work properly, at least in the time period between removing and re-creating the view.

Example 11.6 shows the use of the ALTER VIEW statement.

USE sample;
GO
ALTER VIEW v_without_budget
	AS SELECT project_no, project_name
		FROM project
		WHERE project_no >= 'p3';Code language: PHP (php)

The ALTER VIEW statement in Example 11.6 extends the SELECT statement of the v_without_budget view (see Example 11.2) with the new condition in the WHERE clause.

The DROP VIEW statement removes the definition of the specified view from the system tables. Example 11.7 shows the use of the DROP VIEW statement.

USE sample;
GO
DROP VIEW v_count;Code language: PHP (php)

If the DROP VIEW statement removes a view, all other views derived from it will be dropped, too, as demonstrated in Example 11.8.

USE sample;
GO
DROP VIEW v_clerk;Code language: PHP (php)

The DROP VIEW statement in Example 11.8 also implicitly removes the v_project_p2 view (see Example 11.5). For instance, if you query the v_project_p2 view, you will get the error: “Invalid object name: 'v_clerk‘.”

Editing Information Concerning Views

sys.objects is the most important catalog view concerning views. As you already know, this catalog view contains information in relation to all objects of the current database. All rows of this view that have the value V for the type column contain information concerning views.

Another catalog view called sys.views displays additional information about existing views. The most important column of this view is with_check_option, which instructs you whether or not WITH CHECK OPTION is specified.

Using the system procedure sp_helptext, you can display the query belonging to a particular view.