0
SELECT CONCAT('DROP TABLE `', TABLE_NAME,'`;') 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE 'TABLE_PREFIX_GOES_HERE%';

I've connected to the database. I had run the above query. This query only displays the tables. How do I execute the actual command of deleting of what is being displayed inside the mysql shell?

I tried running a DROP TABLE command against single table which works fine/as expected in the mysql cli shell enviroment.

Shadow
  • 33,525
  • 10
  • 51
  • 64
just_wondering
  • 1
  • You have to execute drop table queries one by one by copy pasting the results as statements. Or save the results as .sql file and run that using mysql cli – Shadow Dec 13 '22 at 15:42
  • 1
    You can execute a prepared statement (only one statement at a time) using mysql, there is an answer here: https://stackoverflow.com/a/37295007/8678978 If you need multiple statements, take a look at the answer here: https://stackoverflow.com/a/38718674/8678978 – chrisbyte Dec 13 '22 at 15:47

1 Answers1

0

you are just "creating" drop statements, you are not executing them. you can either use a cursor and then iterate over it executing the drop command or just push the output of this select via mysql < sqlscript.sql where sqlscript.sql is the output of your query.

(you need to check the mysql syntax to state the db, and maybe also the user and prompt for password as well)

Dani
  • 14,639
  • 11
  • 62
  • 110