SQL guide · Tables, views & databases (DDL)

SQL DROP VIEW: Remove a View From the Database

DROP VIEW removes a view definition from the database. The view is only a stored query, so dropping it does not delete any rows from the underlying tables. What you lose is the name and the saved SELECT, along with any permissions granted on the view. If you might need the query again, copy its definition before you drop it.

You drop a view when it is obsolete, when you are renaming or restructuring things, or when you want to rebuild it with a different set of columns in a database that lacks CREATE OR REPLACE VIEW. Be aware of dependencies: other views, functions and reports may reference the view you remove. Depending on the database, the drop either fails or cascades to those objects, so check before you run it.

Because the statement cannot be undone without a backup or a saved definition, adding IF EXISTS is a good habit in scripts. It lets the script run on a fresh database or a second time without raising an error, and it is the usual first half of a drop-and-recreate pattern for views.

Basic syntax

DROP VIEW [IF EXISTS] view_name [, view_name2, ...];

view_name can be schema-qualified, such as reporting.monthly_revenue. Most databases accept a comma-separated list to drop several views in one statement. PostgreSQL also accepts CASCADE to drop dependent objects or RESTRICT (the default) to refuse when dependencies exist.

SQL DROP VIEW examples

Drop a single view

This removes the active_customers view. The customers table and all of its rows are unaffected, and anyone selecting from the view afterwards gets an error saying it does not exist.

DROP VIEW active_customers;

Drop a view only if it exists

IF EXISTS prevents an error when the view is missing, which makes deployment scripts repeatable. It is supported in PostgreSQL, MySQL, SQLite, and SQL Server 2016 and later.

DROP VIEW IF EXISTS order_details;

Drop several views at once

List multiple views separated by commas to remove them together. In PostgreSQL and MySQL this is one statement, which is convenient when cleaning up a set of temporary reporting views.

DROP VIEW IF EXISTS monthly_revenue, weekly_revenue, daily_revenue;

Drop and recreate a view with a new definition

When CREATE OR REPLACE VIEW cannot be used, for example because the columns change type or order, drop the view first and then create it again. Wrap both statements in a transaction where your database supports transactional DDL so other sessions never see the view missing.

BEGIN;
DROP VIEW IF EXISTS active_customers;
CREATE VIEW active_customers AS
SELECT customer_id, name, email, status
FROM customers
WHERE status IN ('active', 'trial');
COMMIT;

Where SQL dialects differ

PostgreSQL: Supports IF EXISTS, multiple names, CASCADE and RESTRICT. Use DROP MATERIALIZED VIEW for materialized views, because DROP VIEW does not remove them. DDL is transactional, so a drop can be rolled back.

MySQL: Supports DROP VIEW [IF EXISTS] view1, view2. CASCADE and RESTRICT are parsed but ignored. DDL statements commit implicitly, so a drop cannot be rolled back.

SQL Server: Supports DROP VIEW [IF EXISTS] from SQL Server 2016. Earlier versions need a check against sys.views or OBJECT_ID first. Views created with SCHEMABINDING block changes to base tables until the view is dropped.

SQLite: Supports DROP VIEW [IF EXISTS] with a single view name per statement. Schema-qualify the name when working with attached databases.

Oracle: Uses DROP VIEW view_name [CASCADE CONSTRAINTS]. Objects that depend on the view become invalid rather than being removed. Materialized views require DROP MATERIALIZED VIEW. IF EXISTS is available from Oracle 23ai.

Common mistakes

  • Thinking the underlying data is deleted. A drop affects only the view definition, never the base tables, so use DROP TABLE for tables.
  • Using DROP VIEW on a table or DROP TABLE on a view. Both fail, because each statement is specific to its object type.
  • Dropping a view that other views depend on. In PostgreSQL this errors unless you add CASCADE, and CASCADE then removes those dependent views too.
  • Forgetting that permissions are lost. After you recreate a view, grants made on the old one must be applied again in most databases.
  • Not saving the definition first. Keep the CREATE VIEW script in version control so the drop is easy to reverse.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator