Archives of the TeradataForum

Message Posted: Tue, 01 Apr 2003 @ 22:03:36 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

this should do the trick..... you can create this as a view and filter by database name and/or table name.

select db.databasenamei as DatabaseName,
tvm.tvmnamei as TableName,
tvf.fieldname as ColumnName,
cast((case when substr(fieldstatistics,1,1) = 'D3'XB then '2003-'
when substr(fieldstatistics,1,1) = 'D2'XB then '2002-'
when substr(fieldstatistics,1,1) = 'D1'XB then '2001-'
when substr(fieldstatistics,1,1) = 'D0'XB then '2000-'
when substr(fieldstatistics,1,1) = 'CF'XB then '1999-'
when substr(fieldstatistics,1,1) = 'CE'XB then '1998-'
else NULL
end) ||
(case when substr(fieldstatistics,3,1) = '01'XB then '01-'
when substr(fieldstatistics,3,1) = '02'XB then '02-'
when substr(fieldstatistics,3,1) = '03'XB then '03-'
when substr(fieldstatistics,3,1) = '04'XB then '04-'
when substr(fieldstatistics,3,1) = '05'XB then '05-'
when substr(fieldstatistics,3,1) = '06'XB then '06-'
when substr(fieldstatistics,3,1) = '07'XB then '07-'
when substr(fieldstatistics,3,1) = '08'XB then '08-'
when substr(fieldstatistics,3,1) = '09'XB then '09-'
when substr(fieldstatistics,3,1) = '0A'XB then '10-'
when substr(fieldstatistics,3,1) = '0B'XB then '11-'
when substr(fieldstatistics,3,1) = '0C'XB then '12-'
else 'xx-'
end) ||
(case when substr(fieldstatistics,4,1) = '01'XB then '01'
when substr(fieldstatistics,4,1) = '02'XB then '02'
when substr(fieldstatistics,4,1) = '03'XB then '03'
when substr(fieldstatistics,4,1) = '04'XB then '04'
when substr(fieldstatistics,4,1) = '05'XB then '05'
when substr(fieldstatistics,4,1) = '06'XB then '06'
when substr(fieldstatistics,4,1) = '07'XB then '07'
when substr(fieldstatistics,4,1) = '08'XB then '08'
when substr(fieldstatistics,4,1) = '09'XB then '09'
when substr(fieldstatistics,4,1) = '0A'XB then '10'
when substr(fieldstatistics,4,1) = '0B'XB then '11'
when substr(fieldstatistics,4,1) = '0C'XB then '12'
when substr(fieldstatistics,4,1) = '0D'XB then '13'
when substr(fieldstatistics,4,1) = '0E'XB then '14'
when substr(fieldstatistics,4,1) = '0F'XB then '15'
when substr(fieldstatistics,4,1) = '10'XB then '16'
when substr(fieldstatistics,4,1) = '11'XB then '17'
when substr(fieldstatistics,4,1) = '12'XB then '18'
when substr(fieldstatistics,4,1) = '13'XB then '19'
when substr(fieldstatistics,4,1) = '14'XB then '20'
when substr(fieldstatistics,4,1) = '15'XB then '21'
when substr(fieldstatistics,4,1) = '16'XB then '22'
when substr(fieldstatistics,4,1) = '17'XB then '23'
when substr(fieldstatistics,4,1) = '18'XB then '24'
when substr(fieldstatistics,4,1) = '19'XB then '25'
when substr(fieldstatistics,4,1) = '1A'XB then '26'
when substr(fieldstatistics,4,1) = '1B'XB then '27'
when substr(fieldstatistics,4,1) = '1C'XB then '28'
when substr(fieldstatistics,4,1) = '1D'XB then '29'
when substr(fieldstatistics,4,1) = '1E'XB then '30'
when substr(fieldstatistics,4,1) = '1F'XB then '31'
else 'xx'
end) as date) as CollectionDate,
cast(tvf.lastaltertimestamp as date) as LastAlter,
current_date - collectiondate as FromCurrent,
lastalter - collectiondate as FromAlter
from dbc.tvfields tvf
inner join dbc.tvm tvm
on tvm.tvmid = tvf.tableid
and tvm.tablekind in ('t','i')
inner join dbc.dbase db
on tvm.databaseid = db.databaseid
where tvf.fieldstatistics is not null;

	<Prev	Next>		<<First	<Prev	Next>	Last>>