Unfortunately, no command exists in MySQL to drop or truncate all tables in a database. You can drop or truncate each table in a database one-by-one, but even that is cumbersome, as you can’t even pass a comma-delimited list of tables to use.

There’s no command like drop all tables;, neither is there a command that will allow you to drop or truncate multiple tables at once like drop table users, addresses, locations;. You’re left with doing it line by line in a tedious, repetitive manner like so:

drop table users;
drop table addresses;
drop table locations;

If you have a lot of tables, this simply doesn’t work.

If you have a Bash-like shell, you can do it in a much more efficient way via the mysql command and the mysqldump command and some regular expression hackery:

mysqldump -u username --password=password --add-drop-table --no-data database_name | grep ^DROP | mysql -u username --password=password database_name

Winning! If you want to do the same thing for truncating all databases, look no further:

mysqldump -u username --password=password --add-drop-table --no-data database_name | grep ^DROP | sed "s:^DROP TABLE IF EXISTS:TRUNCATE TABLE:g" | mysql -u username --password=password database_name

Now that you’ve emptied your database, it’s time to weep that you forgot to make a backup

via thingy-ma-jig.co.uk

Filed under: Automation, Bash, Databases, bash, databases, grep, mysql, mysqldump, sed, sql