DROP VIEW

Removes a view from the database. The view definition is deleted, but underlying tables are not affected.

For more information on views, see the Views documentation.

Syntax​

DROP VIEW [ IF EXISTS ] view_name

Parameters​

ParameterDescription
IF EXISTSPrevents error if view doesn't exist
view_nameName of the view to drop

Examples​

Drop a view​

Drop view
DROP VIEW my_view

Drop if exists​

To avoid errors when the view might not exist:

Drop view if it exists
DROP VIEW IF EXISTS my_view

Drop multiple views​

Views must be dropped one at a time:

Drop multiple views
DROP VIEW view1;
DROP VIEW view2;
DROP VIEW view3;

Behavior​

  • Dropping a view does not affect the underlying tables
  • Dependent views (views that reference the dropped view) become invalid
  • The view can be recreated later with the same or different definition

Effect on dependent views​

When a view is dropped, any views that reference it become invalid:

Dependent view invalidation
-- Create view hierarchy
CREATE VIEW level1 AS (SELECT * FROM trades WHERE price > 0);
CREATE VIEW level2 AS (SELECT * FROM level1 WHERE quantity > 0);

-- Drop base view
DROP VIEW level1;

-- level2 is now invalid
SELECT view_status FROM views() WHERE view_name = 'level2';
-- Returns: invalid

If the dropped view is later recreated, dependent views automatically become valid again.

Errors​

ErrorCause
view does not exist [view=name]View doesn't exist and IF EXISTS not specified
Access denied [DROP VIEW on view_name]User lacks DROP VIEW permission (Enterprise)

Permissions (Enterprise)​

Dropping a view requires the DROP VIEW permission on that view:

-- Grant DROP VIEW permission to a user
GRANT DROP VIEW ON my_view TO username;

-- Grant DROP VIEW permission on multiple views
GRANT DROP VIEW ON view1, view2 TO username;

When a user creates a view, they are automatically granted all permissions including DROP VIEW on that view.

See also​