I am new to SQL/Teradata so please forgive me if this is a dummy question.
I've been asked to write SQL that will report retention period ( along with number of columns etc) on all DBC.TABLES.
I've put together an SQL to best of my ability to display this information for one table but I need to be able to do it for all the tables.
What is the best way to do this? Thank you in advance
I'm sure SQL I came up with could be better but again, being new to SQL this is the best I could do
, COUNT (*) AS COLUMN_CNT
INNER JOIN (
'D_XYZ' AS DB
,'TABLE_ABC' AS TBL
, MAX(PROC_DT) AS MAX_DT
, MIN(PROC_DT) AS MIN_DT
, (MAX(PROC_DT) - MIN(PROC_DT) MONTH(3) )AS RETENTION
, (SELECT COUNT(*)
WHERE PERF_MTHLY_DT > (ADD_MONTHS(LAST_DAY(DATE),-2)) AND PERF_MTHLY_DT < CURRENT_DATE) AS MTH_CNT
ON (DATABASENAME=DB AND TABLENAME=TBL)
WHERE (TRIM(TABLENAME ) LIKE 'TABLE_ABC' )
AND (DATABASENAME LIKE 'D_%'
OR DATABASENAME LIKE 'T_%' )
GROUP BY 1,2
DatabaseName TableName MAX_DT MIN_DT RETENTION COLUMN_CNT
D_XYZ TABLE_ABC 5/31/2015 1/31/2009 76 293
My goal is to have this info for all DBC.TABLES
Thank you in advance!
I found some information about BTEQ and tried to resolve this with BTEQ, but ran into another issue : I can run bteq in a while loop and get the information I need. But if I have 2000 dbc tables my bteq will log into teradata 2000 times. I think this is not an efficient way to get the results.
done < 1.dat
I've also tried doing BTEQ with REPEAT but in that case I'm not able to dynamically generate 'FROM TABLE'.