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.
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 ;