How to Drop All Tables in MySQL

To drop every table while keeping the MySQL database itself, generate one reviewed DROP TABLE statement for the schema’s BASE TABLE objects, run it in the same session with FOREIGN_KEY_CHECKS temporarily disabled, then verify the result. This permanently removes table definitions and data, so take a backup and confirm that the selected schema is disposable before you run it.

Choose DROP TABLE for this task

DROP TABLE removes tables; it is not the same as DELETE, which removes rows but leaves a table definition, or TRUNCATE, which empties a table. DROP DATABASE is a different and broader operation: use it only when you also intend to remove the database container and its schema-level objects. Dropping a table also removes its triggers and causes an implicit commit, so this is not an operation to test inside a transaction and roll back later.

You need the DROP privilege for the tables you remove. Work from a named schema, use a backup or a known disposable database, and keep the preparation, drop, and restoration statements on the same MySQL connection.

Generate a quoted list of base tables

INFORMATION_SCHEMA.TABLES distinguishes a BASE TABLE from a view. Filtering on table_type = 'BASE TABLE' therefore prevents the drop from treating retained views as tables. The query below also wraps identifiers in backticks and doubles a backtick inside a table name before building the statement.

Set group_concat_max_len high enough for the number and length of names in your schema. The IF expression produces a harmless message for an empty schema instead of attempting DROP TABLE with no names. Review the generated value in @drop_sql before executing it in a production-like environment.

Drop FK-linked base tables in one session

Foreign-key order can make individual drops fail. Save the current session value, disable checks only for this controlled session, execute the generated statement, and restore the saved value. The following is the exact MySQL 8.4.11 fixture used for this article; it creates two FK-linked tables and a view, drops only the tables, and prints the before/after counts.

#!/bin/sh
# Verify that a generated DROP TABLE statement removes FK-linked base tables.
# A view is retained deliberately so the fixture also proves the table/view boundary.
set -eu

fixture_tmp=$(mktemp -d)
trap 'kill "$server_pid" 2>/dev/null || true; rm -rf "$fixture_tmp"' EXIT INT TERM
mysqld --no-defaults --initialize-insecure --datadir="$fixture_tmp/data" --user=root >/dev/null 2>&1
mysqld --no-defaults --datadir="$fixture_tmp/data" --socket="$fixture_tmp/mysql.sock" \
  --pid-file="$fixture_tmp/mysql.pid" --skip-networking --user=root >/dev/null 2>&1 &
server_pid=$!
for _ in $(seq 1 30); do
  mysqladmin --no-defaults --socket="$fixture_tmp/mysql.sock" ping >/dev/null 2>&1 && break
  sleep 1
done
mysqladmin --no-defaults --socket="$fixture_tmp/mysql.sock" ping >/dev/null 2>&1

mysql --no-defaults --batch --raw --skip-column-names --socket="$fixture_tmp/mysql.sock" <<'SQL'
CREATE DATABASE drop_all_demo;
USE drop_all_demo;
CREATE TABLE parent (id INT PRIMARY KEY);
CREATE TABLE child (
  id INT PRIMARY KEY,
  parent_id INT,
  CONSTRAINT child_parent_fk FOREIGN KEY (parent_id) REFERENCES parent(id)
);
CREATE VIEW parent_ids AS SELECT id FROM parent;

SELECT COUNT(*)
FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_type = 'BASE TABLE';

SET SESSION group_concat_max_len = 1048576;
SELECT GROUP_CONCAT(
         CONCAT('`', REPLACE(table_name, '`', '``'), '`')
         ORDER BY table_name SEPARATOR ', '
       )
INTO @table_list
FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_type = 'BASE TABLE';
SET @drop_sql = IF(
  @table_list IS NULL,
  'SELECT ''No base tables found''',
  CONCAT('DROP TABLE IF EXISTS ', @table_list)
);

SET @saved_fk_checks = @@SESSION.foreign_key_checks;
SET SESSION foreign_key_checks = 0;
PREPARE drop_statement FROM @drop_sql;
EXECUTE drop_statement;
DEALLOCATE PREPARE drop_statement;
SET SESSION foreign_key_checks = @saved_fk_checks;

SELECT COUNT(*)
FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_type = 'BASE TABLE';
SELECT COUNT(*)
FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_type = 'VIEW';
SELECT @@SESSION.foreign_key_checks;
SQL

2
0
1
1

The output means there were two base tables initially, zero after the drop, one view still present, and FOREIGN_KEY_CHECKS restored to its original enabled value. This strategy does not delete views, stored procedures, events, users, or the database itself.

Verify the schema after the drop

Run the same INFORMATION_SCHEMA.TABLES count used by the fixture and expect zero BASE TABLE rows. SHOW FULL TABLES FROM your_database is also useful when you need to inspect whether remaining objects are views rather than tables. If your script changes FOREIGN_KEY_CHECKS, check its session value before closing the connection; do not assume another session inherited the change.

Understand the foreign-key failure boundary

Without temporarily disabling checks, MySQL rejects an attempt to drop a referenced parent table. This exact boundary fixture exits with error 3730; it demonstrates why an unordered list of DROP TABLE commands can fail.

#!/bin/sh
# Demonstrate the foreign-key failure when a referenced table is dropped normally.
set -eu

fixture_tmp=$(mktemp -d)
trap 'kill "$server_pid" 2>/dev/null || true; rm -rf "$fixture_tmp"' EXIT INT TERM
mysqld --no-defaults --initialize-insecure --datadir="$fixture_tmp/data" --user=root >/dev/null 2>&1
mysqld --no-defaults --datadir="$fixture_tmp/data" --socket="$fixture_tmp/mysql.sock" \
  --pid-file="$fixture_tmp/mysql.pid" --skip-networking --user=root >/dev/null 2>&1 &
server_pid=$!
for _ in $(seq 1 30); do
  mysqladmin --no-defaults --socket="$fixture_tmp/mysql.sock" ping >/dev/null 2>&1 && break
  sleep 1
done
mysqladmin --no-defaults --socket="$fixture_tmp/mysql.sock" ping >/dev/null 2>&1

mysql --no-defaults --batch --raw --skip-column-names --socket="$fixture_tmp/mysql.sock" <<'SQL'
CREATE DATABASE fk_boundary_demo;
USE fk_boundary_demo;
CREATE TABLE parent (id INT PRIMARY KEY);
CREATE TABLE child (id INT PRIMARY KEY, parent_id INT, CONSTRAINT child_parent_fk FOREIGN KEY (parent_id) REFERENCES parent(id));
DROP TABLE parent;
SQL

The captured diagnostic is ERROR 3730 (HY000): MySQL reports that parent is referenced by child_parent_fk on child. Do not use the disabled-checks technique to bypass an unknown dependency problem; use it only after confirming that deleting every base table is intentional.

Alternatives for disposable databases and command-line automation

For a completely disposable schema, dropping and recreating the database can be simpler, but it is broader than dropping tables. It requires the appropriate database-level privileges and removes objects that the base-table method deliberately leaves behind. Do not describe it as a way to preserve views, routines, or events.

mysqldump --add-drop-table --no-data can produce a shell-oriented drop script, which is useful for automation after review. Avoid putting a password directly on the command line, inspect the generated statements, and keep its credential handling and portability concerns separate from the recommended SQL method.

Limits and troubleshooting

If no tables are dropped, first confirm the selected schema and the BASE TABLE filter. A very large schema can exceed the GROUP_CONCAT limit, so increase it deliberately or generate and review statements in batches. A privilege error means the account lacks DROP access. Views remain by design; remove them separately only when that is part of the approved cleanup. Finally, because DROP TABLE implicitly commits, restore from a backup rather than expecting a rollback to recover dropped data.