How to DROP ALL TABLES in MariaDB

How do I quickly drop every table in a MariaDB database?

DROP all tables removes every table in a MariaDB database by dynamically generating and executing DROP TABLE statements.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does “dropping all tables” really do?

Dropping all tables removes every table and its data from the selected database. Views, triggers, and routines stay intact, but foreign-key relationships vanish with the tables.

When should you drop every table at once?

Use it only in dev or staging when you need a clean slate—such as rebuilding schemas from migrations or loading fresh seed data. Never run it in production without a full backup.

How can you safely drop all tables?

Disable foreign-key checks, generate DROP statements from information_schema, then execute them in a single batch inside a transaction. This approach avoids constraint errors and lets you roll back if something fails.

Step 1: disable foreign-key checks

```sqlSET FOREIGN_KEY_CHECKS = 0;```

Step 2: generate DROP statements

```sqlSELECT GROUP_CONCAT(CONCAT('DROP TABLE IF EXISTS `', table_name, '`') SEPARATOR '; ') INTO @ddlFROM information_schema.tablesWHERE table_schema = 'your_db';```

Step 3: execute the generated SQL

```sqlPREPARE stmt FROM @ddl;EXECUTE stmt;DEALLOCATE PREPARE stmt;SET FOREIGN_KEY_CHECKS = 1;```

Can you wrap everything in a transaction?

Yes. Adding START TRANSACTION; before and COMMIT; after the batch lets you ROLLBACK if an error appears.

Why disable foreign-key checks?

Without it, MariaDB refuses to drop child tables referenced by parents. Disabling checks lets you delete in any order, then re-enable constraints afterward.

Best practice recap

• Always back up first. • Run in off-hours. • Use IF EXISTS to avoid errors. • Re-enable foreign-key checks when done. • Verify with SHOW TABLES;.

Why How to DROP ALL TABLES in MariaDB is important

How to DROP ALL TABLES in MariaDB Example Usage


-- Drop all tables in the ecommerce database
USE ecommerce;
SET FOREIGN_KEY_CHECKS = 0;

SELECT GROUP_CONCAT(CONCAT('DROP TABLE IF EXISTS `', table_name, '`') SEPARATOR '; ') INTO @ddl
FROM information_schema.tables
WHERE table_schema = 'ecommerce';

PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET FOREIGN_KEY_CHECKS = 1;
-- All tables like Customers, Orders, Products, and OrderItems are now gone

How to DROP ALL TABLES in MariaDB Syntax


SET FOREIGN_KEY_CHECKS = 0;

SELECT GROUP_CONCAT(CONCAT('DROP TABLE IF EXISTS `', table_name, '`') SEPARATOR '; ') INTO @ddl
FROM information_schema.tables
WHERE table_schema = 'your_db';

PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET FOREIGN_KEY_CHECKS = 1;

Common Mistakes

Running DROP DATABASE instead of dropping tables. This removes not only tables but also views, routines, and permissions. Use generated DROP TABLE statements to keep the database shell intact.
Forgetting to re-enable FOREIGN_KEY_CHECKS, which leaves the database in a state where new inserts violating constraints silently succeed. Always set it back to 1 after the drop.