DROP VIEW statement

Removes a view from the database.

Syntax

DROP VIEW [ IF EXISTS ] [ owner.]view-name

Remarks

Use the IF EXISTS clause if you do not want an error returned when the DROP VIEW statement attempts to remove a view that does not exist.

When you execute the DROP VIEW statement, the status of all dependent regular views change to INVALID. To determine view dependencies before dropping a view, use the sa_dependent_views system procedure.

If you execute a DROP VIEW statement on a view that has one or more INSTEAD OF triggers, an error is returned. You must drop the trigger before the view can be dropped or altered.

Privileges

You must be the owner of the view, or have the DROP ANY VIEW or DROP ANY OBJECT system privilege.

Side effects

Automatic commit. Executing a DROP VIEW statement closes all cursors for the current connection.

When a view is dropped, all procedures and triggers are unloaded from memory, so that any procedure or trigger that references the view reflects the fact that the view does not exist. The unloading and loading of procedures and triggers can affect performance if you are regularly dropping and creating views.

Standards

ANSI/ISO SQL Standard
Core Feature. The IF EXISTS clause is not in the standard.

Example

The following example creates a view called MyView, and then drops it. You must be able to select from the Employees table to execute the CREATE VIEW statement in the example.

CREATE VIEW MyView
   AS SELECT * FROM GROUPO.Employees;
DROP VIEW MyView;