How to drop multiple tables from one single database at one command. something like,
> use test;
> drop table a,b,c;
where a,b,c are the tables from database test.
This question is related to
mysql
sql
multiple-tables
drop-table
A lazy way of doing this if there are alot of tables to be deleted.
Get table using the below
Copy and paste the table names from the result set and paste it after the DROP command.
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.
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
Source: Stackoverflow.com