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 Databases Database DevelopmentLooking 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 TABLEstatements. Paste and execute these statements in the MySQL/MariaDB command line. -
Exit the MySQL/MariaDB command line: After executing the
DROP TABLEstatements, 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
What do you think?