Teradata - How to find when was table last UPDATED not Altered

user_6488339 0
Viewed 6644

I want to know how to find when was a Teradata table was last UPDATED. Everywhere I look I am getting when was it last Altered. Is updated the same as Altered?

I got the code for Alter table :

SELECT TABLENAME, LASTALTERTIMESTAMP 
FROM DBC.TABLES
WHERE DATABASENAME = 'Schema' 
AND TABLENAME = 'table'
ORDER BY LASTALTERTIMESTAMP DESC

What is it for UPDATE table? Thanks

insert-update teradata sql
3 Answers

Good day!

To find the time the table last was updated, you can try to parse query log table. For example, let your table name is TNAME and it is located in schema SNAME.

So you can use the following query:

select
    username,
    firststeptime,
    querytext
from pdcrinfo.dbqlobjtbl_hst
where 
    statementtype = 'Update'
and querytext like 'insert into SNAME.TNAME%'
qualify row_number() over (partition by 1 order by firststeptime desc) = 1
user_122569990

In terms of SQL requests, UPDATE and ALTER are two different things. ALTER modifies the table structure (DDL) -- i.e. adding / modifying a column. UPDATE modifies the table data (DML) -- i.e. adding / deleting rows.

As far as I remember, there's no easy way to see when a table was last modified (updated) unless you add some custom auditing fields (i.e. lastAlterTimestamp) that you set each time you write to the table.

user_87728880

IF you want to know when a table was updated, you might think about using a trigger.

user_77823520
Related