Drop / Delete tables in bulk

Database
Enthusiast

Drop / Delete tables in bulk

We have a large drive that is badly in need of house keeping. There are a large number of daily tables named as

A_EXM20090101
A_EXM20090102
A_EXM20090103

etc through to

A_EXM20110503
A_EXM20110504
A_EXM20110505

Using Teradat Sql Assist what is the quickest and safest way to drop these tables. I need to keep the 1st working day of every month, otherwise they are all to be deleted. Any help gratefully accepted - otherwise it is going to be a long few days droping these one at a time.

with thanks

Bob

3 REPLIES
Enthusiast

Re: Drop / Delete tables in bulk

If all the daily tables (and no others) are in one database, then you can use the DELETE DATABASE STATEMENT to drop all the tables after copying the tables you want keep to another database.
Enthusiast

Re: Drop / Delete tables in bulk

The following query assumes the first working day on the month is the first monday of the month. You may need to adjust this for your location and/ or to exclude public holidays on a monday.
It also assumes there is a table there for every monday; otherwise it assumes the first monday table it finds is the start of month.

Run the following query, saving the answerset to a UTF-8 text file.
Then open this file as a query and run it. (AFTER CHECKING!)

Select 'Drop Table dbname.'||Trim(Tablename)||';' (Title '')
From DBC.Tables A
Join Sys_Calendar.Calendar B
On Substring(A.Tablename From 6 For 8)
= B.Calendar_Date (Format 'yyyymmdd')
And Databasename = 'TestDB'
And Tablename like 'A^_EXM%' ESCAPE '^'
And (Substring(A.Tablename From 6 For 4),
Substring(A.Tablename From 10 For 2),
B.Calendar_Date) Not In
(Select Year_Of_Calendar, Month_Of_Year,Min(Calendar_Date)
From Sys_Calendar.Calendar
Where Day_Of_Week = 2
Group By 1,2)
Order By 1
;
Enthusiast

Re: Drop / Delete tables in bulk

Jim
Unfortunately there are many other databases in this directory.

Jimm
thank you for all of your work on this, but unfortunately the Monday table is not always the first working day of the month.