DROP DATABASE

Overview

The DROP DATABASE statement permanently deletes a database and all its contents, including tables, views, indexes, stored procedures, and all data.

Warning: DROP DATABASE is irreversible and will permanently delete all data in the database. Use with extreme caution!

Basic Syntax

DROP DATABASE database_name;

Examples

Example 1: Drop a Database

DROP DATABASE company_db;

Example 2: Drop if Exists

-- MySQL
DROP DATABASE IF EXISTS company_db;

-- PostgreSQL
DROP DATABASE IF EXISTS company_db;

-- SQL Server
IF EXISTS (SELECT name FROM sys.databases WHERE name = 'company_db')
DROP DATABASE company_db;

Important Warnings

CRITICAL: DROP DATABASE will:

• Permanently delete all data in the database
• Remove all tables and their contents
• Delete all views, stored procedures, functions, and other objects
• Remove all indexes and constraints
• Cannot be undone unless you have a backup

Safety Checklist

Before dropping a database, verify:

• ✅ You have a recent backup
• ✅ You're connected to the correct database server
• ✅ No active connections are using the database
• ✅ You have the necessary permissions
• ✅ This is the correct database to delete
• ✅ You've confirmed with your team/database administrator

Checking Active Connections

Before dropping a database, you may need to close active connections:

-- SQL Server: Put database in single-user mode
ALTER DATABASE company_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE company_db;

-- PostgreSQL: Terminate connections first
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'company_db'
  AND pid <> pg_backend_pid();

DROP DATABASE company_db;

Database-Specific Syntax

MySQL

DROP DATABASE IF EXISTS company_db;

PostgreSQL

DROP DATABASE IF EXISTS company_db;

SQL Server

-- Basic syntax
DROP DATABASE company_db;

-- With connection handling
ALTER DATABASE company_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE company_db;

Permissions Required

Dropping a database requires high-level privileges:

• MySQL: Requires DROP privilege
• PostgreSQL: Must be database owner or superuser
• SQL Server: Requires DROP DATABASE permission

Alternative: Backup First

Before dropping a database, always create a backup:

-- MySQL: Backup first
mysqldump -u username -p company_db > company_db_backup.sql

-- Then drop
DROP DATABASE IF EXISTS company_db;

-- Restore if needed
mysql -u username -p < company_db_backup.sql
CREATE DATABASE company_db;
mysql -u username -p company_db < company_db_backup.sql

Common Errors

• Database doesn't exist: Use IF EXISTS to avoid error
• Active connections: Close connections first
• Insufficient privileges: Need admin rights
• System databases: Cannot drop system databases

Best Practices

• Always backup first: Never drop without a backup
• Use IF EXISTS: Avoid errors if database doesn't exist
• Double-check name: Verify you're dropping the correct database
• Close connections: Handle active connections before dropping
• Test in development: Practice in non-production environments
• Document reason: Note why the database was dropped

When to Use DROP DATABASE

• Removing obsolete databases
• Cleaning up test/development databases
• Starting fresh with a new database structure
• Removing databases after migration

Next Steps

Learn about creating databases with CREATE DATABASE, or explore table management with CREATE TABLE.