In that case, dropping the database and recreating it is most likely the fastest solution. Database drops in PostgreSQL and MySQL/MariaDB are almost instant. In Oracle it takes ages to (re)create a database.
To get all tables, why not use the DBI::tables? That way you don't have to dig into database specific calls.
If you still want to delete in stead of re-create, why use a prepare/execute combo instead of just do?
$dbh->do ("truncate table $_") for $dbh->tables (undef, undef, undef, "TABLE");
$dbh->do ("delete from $_") for $dbh->tables (undef, undef, undef, "TABLE");
In other databases, you could use delete from table cascade, but iirc that syntax is not supported by mysql where you'd have to create the table to know about cascading deletes.