MySQL Drop View

Last updated: October 19, 2026Oct 19, 2026

Views pile up over time: one for a report that's no longer run, another from an old version of the app. DROP VIEW removes a view's definition. Because a view holds no data, dropping it never deletes rows, but anything that uses the view will stop working.

1. Syntax

DROP VIEW [IF EXISTS] view_name [, view_name ...] [RESTRICT | CASCADE];

• IF EXISTS turns "view doesn't exist" into a note.
• Several views can be dropped at once.
• RESTRICT and CASCADE are accepted but ignored.
• You need the DROP privilege on each view.

2. Dropping a View

SHOW FULL TABLES WHERE Table_type = 'VIEW';

Views before

DROP VIEW old_report;

Query OK, 0 rows affected

The data in members is untouched:

SELECT COUNT(*) AS members_rows FROM members;

Result: 1 row

3. Save the Definition First

Keep a copy of the view's SQL before dropping it, so you can recreate it if needed:

SHOW CREATE VIEW gold_members;

Result: 1 row

4. Dropping Several Views

DROP VIEW gold_members, temp_check;
SHOW FULL TABLES WHERE Table_type = 'VIEW';

Result: 2 rows

If one name in the list doesn't exist, the statement fails and, in MySQL 8.0 and later, no view is dropped: the statement is atomic:

CREATE VIEW v_a AS SELECT 1 AS x;
CREATE VIEW v_b AS SELECT 2 AS x;
DROP VIEW v_a, v_missing, v_b;

Query OK, 0 rows affected
Query OK, 0 rows affected
ERROR 1051 (42S02): Unknown table 'gym.v_missing'

SHOW FULL TABLES WHERE Table_type = 'VIEW';

v_a and v_b still exist

Older MySQL versions (5.7) dropped the existing views and reported the missing one. Use IF EXISTS to make the result the same everywhere.

DROP VIEW v_a, v_b;

Query OK, 0 rows affected

5. IF EXISTS

DROP VIEW IF EXISTS v_missing, active_count;
SHOW WARNINGS;

Query OK, 0 rows affected, 1 warning

Result: 1 row

The existing view active_count was dropped; the missing one only produced a note.

6. Dependent Views Break

MySQL doesn't stop you dropping a view that other views use. Recreate the dependent setup and drop the inner view:

CREATE VIEW active_count AS SELECT plan, COUNT(*) AS members FROM active_members GROUP BY plan;
DROP VIEW active_members;
SELECT * FROM active_count;

Query OK, 0 rows affected
Query OK, 0 rows affected
ERROR 1356 (HY000): View 'gym.active_count' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Find broken views across a database with CHECK TABLE:

CHECK TABLE active_count;

Result: 2 rows

7. Finding Views That Use a Table or View

Before dropping, search the definitions of other views:

SELECT table_name AS view_name
FROM information_schema.views
WHERE table_schema = 'gym' AND view_definition LIKE '%active_members%';

Result: 1 row

8. DROP VIEW Won't Drop a Table

The statements are kept separate to prevent accidents:

DROP VIEW members;

ERROR 1347 (HY000): 'gym.members' is not VIEW

Likewise, DROP TABLE on a view name fails with "Unknown table".

9. Before You Drop a View

1. Save its definition (SHOW CREATE VIEW).
2. Search for dependent views, stored routines and application queries that use it.
3. Check who has privileges on it: SELECT * FROM information_schema.table_privileges WHERE table_name = 'v';
4. Drop it, then run CHECK TABLE on remaining views to catch breakage.

10. Summary

• DROP VIEW v; removes a view's definition; base table data is never affected.
• Drop several views at once; use IF EXISTS in scripts.
• Dependent views are not checked and will break; find them in information_schema.views.
• Save the definition first with SHOW CREATE VIEW.