Menu
DROP TABLE ( SELECT table_name FROM information_schema.`TABLES` WHERE table_schema = 'myDatabase' AND table_name LIKE BINARY 'del%'); I know this doesn't work! What is the equivalent for something like this in SQL? I can whip out a simple Python script to do this but was just wondering if we can do something with SQL directly. I am using MySQL. Thank you!
315
WHERE clause mainly used along with the DELETE command. No clause required along with DROP command. Actions performed by DELETE can be rolled back as it uses buffer. Actions performed by DROP can't be rolled back because it directly works on actual data.
We can use the following syntax to drop multiple tables: DROP TABLE IF EXISTS B,C,A; This can be placed in the beginning of the script instead of individually dropping each table. Maybe it's worth pointing out that the tables don't need to have any relationship at all.
To remove a table in MySQL, use the DROP TABLE statement. The basic syntax of the command is as follows: DROP [TEMPORARY] TABLE [IF EXISTS] table_name [, table_name] [RESTRICT | CASCADE];
SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) AS statement FROM information_schema. tables WHERE table_name LIKE 'myprefix_%'; This will generate a DROP statement which you can than copy and execute to drop the tables.
You can use prepared statements -
SET @tables = NULL; SELECT GROUP_CONCAT('`', table_schema, '`.`', table_name,'`') INTO @tables FROM information_schema.tables WHERE table_schema = 'myDatabase' AND table_name LIKE BINARY 'del%'; SET @tables = CONCAT('DROP TABLE ', @tables); PREPARE stmt1 FROM @tables; EXECUTE stmt1; DEALLOCATE PREPARE stmt1; It will generate and execute a statement like this -
DROP TABLE myDatabase.del1, myDatabase.del2, myDatabase.del3; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
