To start with. You can’t. Not directly, anyway. There is no “drop table prefix_%;”, so stop looking.
This seems to be the easiest workaround that we’ve found based on information shared here.
So here’s how we did it. For reference, anywhere a word or a phrase is wrapped in greater than / less than signs, that’s where you use your own <information>.
First you need to get into mySQL via ssh.
mysql -u <database-name> -p
Then key in your password to get to the mysql command prompt. Once there you need to find the actual database you want to work with:
show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| yaaygnacua |
+--------------------+
2 rows in set (0.001 sec)
99% of the time it will be “information_schema” and the database you want (in the above example it’s “yaaygnacua”). Then use the database:
use <database>;
and make sure you’re looking at the right tables by using:
show tables;
Once you’ve decided what your wildcard can be key this line in with your wildcard:
SELECT CONCAT( "DROP TABLE ", GROUP_CONCAT(TABLE_NAME) ) AS stmt FROM information_schema.TABLES WHERE TABLE_NAME LIKE "<wildcard>%";
Assuming your wildcard hits something, this will generate a string that will look similar to this:
DROP TABLE mucvkd_wp_ebay_store_categories,mucvkd_wp_woocommerce_tax_rate_locations,mucvkd_wp_ebay_jobs,mucvkd_wp_mailchimp_jobs,mucvkd_wp_wfconfig,mucvkd_wp_wftrafficrates,mucvkd_wp_wflivetraffichuman
DROP TABLE mucvkd_wp_ebay_store_categories,mucvkd_wp_woocommerce_tax_rate_locations,mucvkd_wp_ebay_jobs,mucvkd_wp_mailchimp_jobs,mucvkd_wp_wfconfig,mucvkd_wp_wftrafficrates,mucvkd_wp_wflivetraffichuman;
Copy and paste that code right back into mysql, and most importantly, add a semi-colon at the end.
And voilà, your tables are gone.
Generally a tool my phpMyAdmin would be a much faster solution to dropping multiple tables, but when you’ve got a database with thousands of spurious tables, those types of applications will usually choke when trying to display, select, and delete that many tables.
Quick Caution
If your wildcard is too loose (as in result below only using “i”), you’ll get spurious results. It seems that the spurious result are all upper case, but do your own due diligence. Just grab the actual table names and paste them after a “drop tables” command. (and don’t forget the trailing semi-colon).
DROP TABLE INDEX_STATISTICS,INNODB_SYS_DATAFILES,INNODB_SYS_TABLESTATS,INNODB_LOCKS,INNODB_MUTEXES,INNODB_CMPMEM,INNODB_CMP_PER_INDEX,INNODB_CMP,INNODB_FT_DELETED,INNODB_CMP_RESET,INNODB_LOCK_WAITS,INNODB_TABLESPACES_ENCRYPTION,INNODB_BUFFER_PAGE_LRU,INNODB_SYS_FIELDS,INNODB_CMPMEM_RESET,INNODB_SYS_COLUMNS,INNODB_FT_INDEX_TABLE,INNODB_CMP_PER_INDEX_RESET,INNODB_FT_INDEX_CACHE,INNODB_SYS_FOREIGN_COLS,INNODB_FT_BEING_DELETED,INNODB_BUFFER_POOL_STATS,INNODB_TRX,INNODB_SYS_FOREIGN,INNODB_SYS_TABLES,INNODB_FT_DEFAULT_STOPWORD,INNODB_FT_CONFIG,INNODB_BUFFER_PAGE,INNODB_SYS_TABLESPACES,INNODB_METRICS,INNODB_SYS_INDEXES,INNODB_SYS_VIRTUAL,INNODB_TABLESPACES_SCRUBBING,INNODB_SYS_SEMAPHORE_WAITS,ihxfoh_wp_yoast_seo_links,ihxfoh_wp_wfconfig,ihxfoh_wp_gpi_page_blacklist,ihxfoh_wp_wfblockediplog,ihxfoh_wp_postmeta,ihxfoh_wp_wftrafficrates,ihxfoh_wp_term_taxonomy,..
Quick Caution 2
If your wildcard is too loose, you may get more results that mysql will return. Keep your eyes open for partial table names. Just grab the actual table names and paste them after a “drop tables” command. (and don’t forget the trailing semi-colon). If you accidentally try to drop a partial table, you won’t hurt anything, you’ll just get an error “ERROR 1051 (42S02): Unknown table”