SQL: deleting tables with prefix

by

You cannot do it with just a single MySQL command, however you can use MySQL to construct the statement for you:

In the MySQL shell or through PHPMyAdmin, use the following query

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

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

EDIT: A disclaimer here – the statement generated above will drop all tables in all databases with that prefix. If you want to limit it to a specific database, modify the query to look like this and replace database_name with your own database_name:

SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) 
    AS statement FROM information_schema.tables 
    WHERE table_schema="database_name" AND table_name LIKE 'myprefix_%';

More Related Contents:

  1. i have an error which i dont understand. can you help me? [closed]
  2. If two columns are 1 then make new column
  3. How to display UTF-8 characters in phpMyAdmin?
  4. #1071 – Specified key was too long; max key length is 1000 bytes
  5. MySQL : isn’t in GROUP BY
  6. MySQL: Can’t create table (errno: 150)
  7. Restore table structure from frm and ibd files
  8. Subqueries with EXISTS vs IN – MySQL
  9. phpMyAdmin on MySQL 8.0 [duplicate]
  10. Find and Replace text in the entire table using a MySQL query
  11. Setting up foreign keys in phpMyAdmin?
  12. Is there a simple way to convert MySQL data into Title Case?
  13. Bug? #1146 – Table ‘xxx.xxxxx’ doesn’t exist
  14. Creating functions in phpMyAdmin – Error: access denied you need the super privilege for this operation
  15. #1273 – Unknown collation: ‘utf8mb4_unicode_ci’ cPanel
  16. Error Dropping Database (Can’t rmdir ‘.test\’, errno: 17)
  17. phpMyAdmin is throwing a #2002 cannot log in to the mysql server phpmyadmin
  18. MySQL said: Documentation #1045 – Access denied for user ‘root’@’localhost’ (using password: NO)
  19. How to Import Multiple csv files into a MySQL Database
  20. phpMyAdmin – can’t connect – invalid setings – ever since I added a root password – locked out
  21. Can’t import database through phpmyadmin file size too large
  22. Access denied after setting user’s password with SHA256 in phpMyAdmin
  23. where does MySQL store database files?
  24. #1292 – Incorrect date value: ‘0000-00-00’ [duplicate]
  25. #1130 – Host ‘localhost’ is not allowed to connect to this MySQL server
  26. How do I write an SP in phpMyAdmin (MySQL)?
  27. MySQL DROP all tables, ignoring foreign keys
  28. How to import large sql file in phpmyadmin
  29. Import CSV to Update rows in table
  30. mySQL query to search all tables within a database for a string?

Leave a Comment