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