How to Drop All Tables of a MySQL / MariaDB Schema At Once

Master MySQL/MariaDB table drops with our tutorial. From command-line to scripts, handle foreign keys, backup data, and streamline database management.

published on 7 Jan 2024 in Web DevelopmentDatabases

Looking for a short way to drop all tables of a MySQL or MariaDB database? This is a quick tutorial showing how to delete all tables of a MySQL / MariaDB database in a couple of easy steps.

To drop all tables in a MySQL or MariaDB schema at once, you can use a combination of SQL queries and command-line tools. Here's one way to do it:

Using Command Line:


  
  mysqldump -u [username] -p [database_name] > backup.sql

  
  mysql -u [username] -p

  
  SELECT CONCAT('DROP TABLE IF EXISTS ', table_name, ';') 
  FROM information_schema.tables 
  WHERE table_schema = '[your_database_name]';

  
  DROP TABLE IF EXISTS table1;
  DROP TABLE IF EXISTS table2;

  
  exit

Access the MySQL/MariaDB command line: Enter the MySQL/MariaDB command line with your username and password.

mysql -u [username] -p

Run SQL queries to generate DROP TABLE statements: Once you are in the MySQL/MariaDB command line, run the following query to generate a list of DROP TABLE statements for each table in the database:

SELECT CONCAT('DROP TABLE IF EXISTS ', table_name, ';') 
FROM information_schema.tables 
WHERE table_schema = '[your_database_name]';

Copy the result and execute the statements: Copy the output of the above query, which will be a series of DROP TABLE statements. Paste and execute these statements in the MySQL/MariaDB command line.
Exit the MySQL/MariaDB command line: After executing the DROP TABLE statements, exit the MySQL/MariaDB command line:

exit

Using a Script:

If you prefer using a script, you can create a simple Bash script to automate the process:

#!/bin/bash DB_NAME="your_database_name" DB_USER="your_username" DB_PASSWORD="your_password" # Backup mysqldump -u $DB_USER -p$DB_PASSWORD $DB_NAME > backup.sql # Generate and execute DROP TABLE statements mysql -u $DB_USER -p$DB_PASSWORD -Nse "SHOW TABLES" $DB_NAME | while read table; do mysql -u $DB_USER -p$DB_PASSWORD -e "DROP TABLE IF EXISTS $table" $DB_NAME done

Make sure to replace your_database_name, your_username, and your_password with your actual database name, username, and password.

Save the script, make it executable (chmod +x script.sh), and then run it (./script.sh).

Remember to handle sensitive information, such as usernames and passwords, securely, and always be cautious when performing operations that modify or delete data.

Bypassing the Foreign Key Restrictions

When it comes to deleting relational database tables, you may encounter issues with foreign key relationships that prevent the execution of drop commands. This can lead to errors such as “MySQL error 1217 – Cannot delete or update a parent row: a foreign key constraint fails”.

Open a new line at the top of the drop statements and insert the following one line code so as to make it the first command of all statements:

SET FOREIGN_KEY_CHECKS = 0;

Go to the end of the last statement, and type or copy the following:

SET FOREIGN_KEY_CHECKS = 1;

Replace [your_database_name] with the name of your database.

Revised version of the above commands and script code to bypass the foreign key restrictions would be like the following:

Drop All Tables in MySQL/MariaDB Schema with Foreign-Key Handling

Using Command Line:

mysqldump -u [username] -p [database_name] > backup.sql mysql -u [username] -p SET FOREIGN_KEY_CHECKS = 0; SELECT CONCAT('DROP TABLE IF EXISTS ', table_name, ';') FROM information_schema.tables WHERE table_schema = '[your_database_name]'; DROP TABLE IF EXISTS table1; DROP TABLE IF EXISTS table2; SET FOREIGN_KEY_CHECKS = 1; exit

Using a Script:

#!/bin/bash DB_NAME="your_database_name" DB_USER="your_username" DB_PASSWORD="your_password" # Backup mysqldump -u $DB_USER -p$DB_PASSWORD $DB_NAME > backup.sql # Generate and execute DROP TABLE statements with foreign-key handling mysql -u $DB_USER -p$DB_PASSWORD -e "SET FOREIGN_KEY_CHECKS = 0;" $DB_NAME mysql -u $DB_USER -p$DB_PASSWORD -Nse "SHOW TABLES" $DB_NAME | while read table; do mysql -u $DB_USER -p$DB_PASSWORD -e "DROP TABLE IF EXISTS $table" $DB_NAME done mysql -u $DB_USER -p$DB_PASSWORD -e "SET FOREIGN_KEY_CHECKS = 1;" $DB_NAME

Share this on

Comments

What do you think?

stamcant
Great tutorial! I'm concerned about accidentally dropping tables that might still be referenced elsewhere—how can I ensure only unused tables are deleted?

March 2025

Reply
- Ozar
  
  Great question! Accidentally dropping a table that's still in use can really mess up your database. Here’s how I usually play it safe when cleaning out tables:
  
  Check for Foreign Keys. Some tables might be referenced by others. You can run this query to see which tables are being referenced:
  SELECT TABLE_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'your_database_name';
  If a table shows up here as REFERENCED_TABLE_NAME, something is pointing at it.
  
  See What’s Active. Sometimes, just checking which tables are still being changed can help spot the “live” ones:
  SELECT TABLE_NAME, UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = 'your_database_name';
  If a table’s UPDATE_TIME is recent, maybe it’s not so safe to drop.
  
  Search Your Codebase. Take a look through your app’s source code for any mention of the tables you want to drop. If you find a match, it's probably still in use.
  
  Ask Around. If you’re working with others, double-check with the team before deleting anything.
  
  Some extra safety tips:
  
  Always back up your database first!
  Here’s a quick way with mysqldump:
  mysqldump -u your_username -p your_database_name > backup.sql
  That way you can restore things if you goof.
  
  Still nervous? Instead of dropping, try renaming the tables first (like adding _old). Wait a few weeks—if nobody screams or the app doesn't throw errors, you’re probably safe to delete them.
  
  By taking these steps, you’ll be much less likely to remove something important by accident. Good luck—and always have a backup!
  
  June 2025
  
  Reply