Drop multiple tables in one shot in MySQL

Question

How to drop multiple tables from one single database at one command  something like    gt  use test    gt  drop table a b c    where a b c are the tables from database test

User · Answer

A lazy way of doing this if there are alot of tables to be deleted.

Get table using the below
- For sql server - SELECT CONCAT(name,',') Table_Name FROM SYS.tables;
- For oralce - SELECT CONCAT(TABLE_NAME,',') FROM SYS.ALL_TABLES;
Copy and paste the table names from the result set and paste it after the DROP command.

User · Answer

declare  sql1 nvarchar max   SELECT  sql1     STUFF                        select   drop table dbo      name                   FROM sys sysobjects AS sobjects            WHERE  xtype    U   AND  name LIKE  GROUP BASE NEW WORK                for xml path                     1  1         execute sp executesql  sql1

User · Answer

SET foreign key checks   0  DROP TABLE IF EXISTS a b c  SET foreign key checks   1    Then you do not have to worry about dropping them in the correct order  nor whether they actually exist   N B  this is for MySQL only  as in the question   Other databases likely have different methods for doing this

User · Answer

Example   Let s say table A has two children B and C  Then we can use the following syntax to drop all tables   DROP TABLE IF EXISTS B C A    This can be placed in the beginning of the script instead of individually dropping each table

[mysql] Drop multiple tables in one shot in MySQL

Examples related to mysql

Examples related to sql

Examples related to multiple-tables

Examples related to drop-table