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.
4 Answers
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.
answered Feb 7, 2011 at 14:12
-
36Maybe it's worth pointing out that the tables don't need to have any relationship at all. They can be completely independent and this syntax will still work.– crmpiccoCommented Feb 19, 2014 at 10:11
-
2022, gives syntax error in
,. Will post a solution if I find one.– SidCommented Nov 22, 2022 at 2:59 -
@Sid works fine for me on MySQL 5.7, make sure you use backticks "`" to surround names– Volatil3Commented Dec 9, 2022 at 12:04
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.
answered Feb 7, 2011 at 14:18
-
5You've saved me from lots of hassle by turning of fkey checks (y). Commented Jul 11, 2014 at 3:18
-
I still get # 1451 - Deleting or updating a parent record fails due to a foreign key restriction Edit: i was running this in phpmyadmin, and the checkbox for foreign key checks was still on. I think you dont need the
SET foreign_key_checksquery there, just use the checkbox.– BlackCommented Jul 20, 2021 at 14:10
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.
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
DROP TABLEinto a query and double click each desired table to append its name to the query (put a comma between them) then hit F9 to execute. A bit off-topic but I came here for that.DROP TABLE table_prefix_*does not work with the star character: stackoverflow.com/questions/6758652/…