0

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.

Cole
  • 317
  • 4
  • 14
  • You could get a list of databases with the table from information schema, the use that to write the drop table statements and execute them in a for next loop. – Sloan Thrasher Apr 26 '17 at 00:02
  • What do you mean across databases? you can delete table name specifically even you want to use a loop of it.Regardless if you want to delete all table from that specific database – Vijunav Vastivch Apr 26 '17 at 00:43
  • I am referring to deleting the same table across multiple databases. Say for instance you had 200 databases that all have the same table structure, and you no longer need a few tables. I was inquiring about how to get a list of all the databases and the table and run a delete table on all those databases. – Cole Apr 26 '17 at 07:08

2 Answers2

1

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.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • The top script worked great for getting a list output. I can export that list or copy it pretty easily in Sequel Pro. Could you elaborate more on what that script would look like or can I just run the second part you put in by replacing the top line and it will execute the same thing as the script? – Cole Apr 26 '17 at 07:19
  • Running the SELECT statement just generates a resultset. The result happens to be a set of DROP statements. (I'm just using SQL to generate SQL.) It's two step process, 1) generate the DROP statements, and 2) as a separate step. execute the DROP statements. We need a client program to do that... 1) run the SELECT and 2) execute the DROP statements. The only way to get that done in a single call to the database server would be to call a MySQL stored procedure. The procedure would be the client performing the two steps. (Obviously, we would first have to write that procedure.) – spencer7593 Apr 26 '17 at 13:58
  • This worked great. I was able to easily paste the first command to get the drop statements in the query section of sequel pro. After copying the drop statements and pasting them into a new query area, I could run that and it ran each query and deleted the tables. Thank you for the help. – Cole Apr 27 '17 at 01:59
0

This is the spencer7593's "Drop query" slightly adaptated under Mysql 5.6.20 and Phpmyadmin :

select concat ('DROP TABLE ',table_schema,'.',table_name,';') FROM information_schema.tables where table_name like '%searched_pattern%' limit 0,500

Limit to 500 because Phpmyadmin displays only the 25 firsts results and I wanted to copy-paste all the results in a new SQL window.

Running Duck
  • 11
  • 2