How to delete taggregate_temp_ tables at once?

Question

In my SQL I have multi tables like:

taggregate_temp_1364792160
taggregate_temp_1364795760
taggregate_temp_1364799360
taggregate_temp_1364802960
taggregate_temp_1364806560
taggregate_temp_1364810160
taggregate_temp_1364813760
taggregate_temp_1364817360
taggregate_temp_1364820960
taggregate_temp_1364824560
taggregate_temp_1364828160
taggregate_temp_1364831760
taggregate_temp_1364835360
taggregate_temp_1364838960
taggregate_temp_1364842560
taggregate_temp_1364846160
taggregate_temp_1364849760
taggregate_temp_1364853360

I need to delete all tables begin with: taggregate_temp_ at once
Can you help me please?

score 0 · Accepted Answer · edited May 23 '17 at 11:53

0

This question was answered by another user: Drop all tables whose names begin with a certain string

You will want to do something like

USE DATABASE_NAME
GO

declare @cmd varchar(4000)
declare cmds cursor for 

select 'DROP TABLE [' + Table_Name + ']'
from INFORMATION_SCHEMA.TABLES
where Table_Name like 'taggregate_temp_%'

open cmds
while 1=1
begin
    fetch cmds into @cmd
    if @@fetch_status != 0 break
    exec(@cmd)
end
close cmds;
deallocate cmds

Edit: You didn't mention mysql in the question, but I just saw the tag, have a look at the following topic for a mysql example: MySQL bulk drop table where table like?

edited May 23 '17 at 11:53

Community

1
1

answered Jun 02 '14 at 23:44

Tsarin

171
4

Do I need to replace DATABASE_NAME ? – tahafahed Jun 03 '14 at 01:44
Yes, you'll need to define which database you want to run the command against. – Tsarin Jun 03 '14 at 03:32
Do I need to replace Table_Name ? – tahafahed Jun 03 '14 at 12:14
Nope, Table_Name is the name of the column in the information schema. In the edit I mentioned that this sql is specific to SQL Server, but there is a link to a MySQL specific example – Tsarin Jun 03 '14 at 23:47

How to delete taggregate_temp_ tables at once?

1 Answers1