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

Examples

Drop a view

DROP VIEW my_view

Drop if exists

To avoid errors when the view might not exist:

DROP VIEW IF EXISTS my_view

Drop multiple views

Views must be dropped one at a time:

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:

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

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

• Views concept
• CREATE VIEW
• ALTER VIEW
• COMPILE VIEW