Introduction
Database maintenance requires removing obsolete or incorrect views.
DROP VIEW statement deletes view definitions from database.
It is widely used for:

  • Cleaning obsolete queries
  • Schema refactoring
  • Development cleanup

What is DROP VIEW
DROP VIEW removes view definition, not underlying data.
Base tables remain unaffected.

Basic Syntax

sql

DROP VIEW view_name; 

Example

sql


Removes ActiveStudents view definition.

DROP VIEW IF EXISTS
Safe deletion (no error if missing).

sql


Multiple Views
Drop several views at once.

sql


DROP VIEW with Schema
Specify database explicitly.

sql


CASCADE vs RESTRICT
Control dependent object handling.

sql


Check Before Drop
Verify view existence first.

sql


Drop with Permissions
Requires DROP privilege on views.

sql

-- Grants: DROP VIEW on database.views 

Drop All Views in Schema
Dynamic cleanup (use carefully).

sql


 SET @views = CONCAT('DROP VIEW IF EXISTS ', @views);


Drop View Dependencies

Views referencing dropped view fail.

sql


Execution Order with DROP VIEW
Immediate removal → Invalidate dependent objects → Commit

Performance Considerations

  • Instant operation (metadata only)
  • No data affected
  • Large numbers: use scripting

Important Notes

  • Only removes view definition
  • Base table data unaffected
  • Dependent views become invalid
  • IF EXISTS prevents errors
  • Requires DROP privilege
  • Transaction-safe

Example Scenario
Clean up development views:

sql


Common Mistakes

  • Dropping without backup (recreate needed)
  • Missing IF EXISTS (errors)
  • Insufficient DROP privileges
  • Dependent view breakage
  • Forgetting schema specification

Key Points to Remember

  • DROP VIEW removes definition only
  • IF EXISTS prevents errors
  • Multiple views: comma-separated
  • CASCADE drops dependencies
  • Base tables completely unaffected
  • Requires DROP privilege