phpmyadmin Question posted in
The official documentation can be found here.

DROP all table with a specific name prefix – Phpmyadmin

I have a database which store old installation wordpress tables.
I would like to clean up a bit so I am looking for a query can delete the table based on the prefix table name.

In this case I want to drop all table that start with “_wordpress_1_”

   _wordpress_1_eruiweuriw
   _wordpress_2_jshfiojdwi
   _wordpress_1_fksdjfksdj
   _wordpress_1_fskdjfksdf
   _wordpress_3_kfsjdfsdkf

So iam trying:

 USE database_name;
 SELECT * FROM database_name WHERE TABLE_NAME LIKE '%_wordpress_1_%';

I guess i cannot use like in the where statement for table name but just for table column??
Could I get any tips on how to achive this?

Tags:

2

Answers


  1. 0

    you don’t can do it with only one MySQL Command, you can use MySQL to construct the statement for you:

    In the MySQL shell or PHPMyAdmin, Paste the following query with changing the prefix : 

    SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) 
AS statement FROM information_schema.tables 
WHERE table_name LIKE '_wordpress_1_%';

    This will generate a DROP statement which you can than copy and execute to drop the tables.

    Login or Signup to reply.
  2. 0 
    CREATE PROCEDURE drop_all_tables (IN dbase TEXT, IN prefix TEXT)
BEGIN
SELECT CONCAT('DROP TABLE ', GROUP_CONCAT(table_name))
INTO @sql
FROM information_schema.tables 
WHERE table_schema = dbase 
  AND table_name LIKE CONCAT(prefix, '%');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
-- and now cry if you make a mistake in parameters
END

    then

    CALL drop_all_tables ('wordpress_database', '_wordpress_1_');
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search