All Forums Database
Mystical 9 posts Joined 12/10
05 May 2011
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


etc through to


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


Jim Chapman 449 posts Joined 09/04
05 May 2011

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.

Jimm 298 posts Joined 09/07
05 May 2011

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

Mystical 9 posts Joined 12/10
06 May 2011

Unfortunately there are many other databases in this directory.

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

You must sign in to leave a comment.