Let’s see how you can remove all tables in MySQL database from the command-line if you don’t have DROP database permissions.

1. Log into mysql with your credentials:

   mysql -u{username} -p{password}

2. Create, prepare and execute statement

   SET FOREIGN_KEY_CHECKS = 0; 
   SET @tables = NULL;
   SELECT GROUP_CONCAT(table_schema, '.', table_name) INTO @tables
     FROM information_schema.tables 
     WHERE table_schema = 'database_name'; -- specify DB name here.
   
   SET @tables = CONCAT('DROP TABLE ', @tables);
   PREPARE stmt FROM @tables;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;
   SET FOREIGN_KEY_CHECKS = 1;

   please don’t forget type your database name instead of “database_name“

3. If you have many table in your db (as like in db for Magento CMS) you should increase max length for GROUP_CONCAT

    SET SESSION group_concat_max_len = 100000;

That’s it. Please be careful and create backup before run commands from this post.

Facebook

Google

Twitter

Linkedin

Print

Email