Is there a way to delete a table across multiple databases using MYSQL? I am using Sequel Pro but don't see any option within the GUI to do this so I am wondering if there is some sort of command that can do this?
The table name is the same across all the databases, and removing them one by one is time consuming. This would really help with cleaning up databases as we have hundreds and they all share the same tables. However, some tables are no longer needed and need to be deleted.
Hoping someone can help with a query for doing this.
You would need to run an individual DROP statement for each table.
We can query information_schema.tables to get a list of tables...
SELECT t.table_schema, t.table_name
FROM information_schema.tables t
WHERE t.table_name = 'tablename_i_want_to_drop'
AND t.table_schema NOT IN ('mysql','information_schema','performance_schema')
ORDER BY t.table_schema, t.table_name
And we can use an expression instead of the columns ...
SELECT CONCAT('DROP TABLE `',t.table_schema,'`.`',t.table_name,'` ;') AS `-- stmt`
FROM ...
Then we can take that resultset and save it, and execute the statements from a script. The MySQL command line client allows us to source a script...
https://dev.mysql.com/doc/refman/5.7/en/mysql-batch-commands.html
The client you are using may have a similar feature, to execute a script. Or somehow copy the resultset and paste that into a query window.
In a MySQL stored program, we could run that same query (to get a list of tables), and then loop those (using a CURSOR), and PREPARE, EXECUTE and DEALLOCATE PREPARE to execute "drop table" statements. That's an option, but for a one time shot, creating the script would be easier.