DROP all MySQL tables from the command line
I had a situation
today where I wanted to drop all the tables in a database, but didn't have
access to a UI like phpMyAdmin. The easiest thing to do would have been to drop
the entire database then re-create it, but I didn't have permissions to drop an
entire database.
After searching
around online, this is the best solution I found:
mysqldump
-u username -p --no-data dbname | grep ^DROP > drop.sql
mysql
-u username -p dbname < drop.sql
rm drop.txt
Simple, and only
requires 2 lines (ok, a 3rd line for cleanup).
What these commands
do is basically generated a mysqldump file (which included DROP commands by
default), then filter the output to only show the lines that have the DROP
statements. The arrow (>) redirects the output to a file. The second line
then imports those SQL statements into the database.
Another option I
have is to use information_schema database and generate an sql script like
below :
Select concat('DROP TABLE database_name.', table_name,';') from information_schema.TABLES where table_schema='database_name';
Which gives:
DROP TABLE database_name.table_name1;
...
DROP TABLE database_name.table-nameN;
You can pipe it to
be executed directly. I prefer this syntax than the DROP TABLE IF EXISTS
table_name1;
Note :
Replace database_name with your DB Name. Also, sometime if we execute generated
script, then it may throw exceptions if tables have dependencies with each
other. In such case you have to first delete master table and then dependent
table.
