0

I have a information schema of tables, say it contains

aaaa_tale
bbbb_table
cccc_table
dddd_table

I have another table that contains a list of table names. 

cccc_table
bbbb_table

Is there a command I can execute that will allow me to drop the tables in the schema that are not found in the table containing table names? So afterwards only aaaa_table and dddd_table are left in the schema.

brianthelion
  • 33
  • 1
  • 8
  • 1
    See http://stackoverflow.com/questions/21633115/return-row-only-if-value-doesnt-exist?lq=1 for how to get all the rows in one table that don't have a match in a second table. Then use this query in a stored procedure that prepares a `DROP TABLE` statement. – Barmar Aug 30 '16 at 00:17

1 Answers1

0

Assuming the table that has the list of tables not to be dropped is named "table_list" with the list in a "table_name" column. If you want to use pure SQL, you can do one of these depending on what tables you want to retain in the schema (not clear from your question):

Case: You want to drop the tables listed in the list of tables, so that only aaaa_table and dddd_table remain (refer to the last sentence of your question).

SET GROUP_CONCAT_MAX_LEN=10000; /* Adjust this as per your requirement */
SELECT CONCAT( 'DROP TABLE ',
GROUP_CONCAT(TABLE_NAME) ) INTO @drop_query
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = database()  
AND TABLE_NAME IN (SELECT table_name from table_list);
SET @drop_query = IFNULL(@drop_query, 'SELECT 1'); 
PREPARE drop_statement FROM  @drop_query;
SET foreign_key_checks = 0;
EXECUTE drop_statement;
SET foreign_key_checks = 1;
DEALLOCATE PREPARE drop_statement;

Case: You want to retain the tables listed in the list of tables, so that only cccc_table and dddd_table remain (refer to the second-last sentence of your question, especially "allow me to drop the tables in the schema that are NOT found in the table containing table names").

SET GROUP_CONCAT_MAX_LEN=10000; /* Adjust this as per your requirement */
SELECT CONCAT( 'DROP TABLE ',
GROUP_CONCAT(TABLE_NAME) ) INTO @drop_query
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = database()  
AND TABLE_NAME NOT IN (SELECT table_name from table_list);
SET @drop_query = IFNULL(@drop_query, 'SELECT 1'); 
PREPARE drop_statement FROM  @drop_query;
SET foreign_key_checks = 0;
EXECUTE drop_statement;
SET foreign_key_checks = 1;
DEALLOCATE PREPARE drop_statement;

In both cases, you can avoid the hack to check for NULL and if found, substitute with a dummy query ("SELECT 1") in the above code by using a stored procedure. A stored procedure will allow you to execute a conditional block only if @drop_query is not NULL.

Pranay Palni
  • 16
  • 5
  • thank you so much for your help and time! I did some research/further expansion on your helpful code and was able to figure it out using prepared statements in a way very similar to this. Sorry for the lack of clarity in my question! – brianthelion Aug 30 '16 at 22:08
  • @brianthelion You're very welcome, glad to be of assistance :) – Pranay Palni Aug 30 '16 at 22:12
  • @brianthelion Please mark my answer as "Answered", but only if you found it to be an correct, efficient and optimal resolution of your question. Thanks. – Pranay Palni Aug 30 '16 at 22:45