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.
The original suggestion I found looked like this:
mysqldump -u username --no-data dbname | grep ^DROP | mysql -u username
The only problem I ran into was I needed to specify a password. When I added the -p password argument, the first mysqldump command got passed an empty password. I could have included the password in the command itself, but that's a security no no.
Comments
An other way
You can also generate an sql script from the information_schema database:
Select concat('DROP TABLE database_name.', table_name,';') from information_schema.TABLES where table_schema='database_name';Which gives:
You can pipe it to be executed directly (I like to verify first ;-) ) I prefer this syntax than the DROP TABLE IF EXISTS table_name1;
Post new comment