Find the time when table was last accessed in Teradata database

Article for: SQL Server IBM Db2 Vertica

Query below returns list of tables and their last using date.

Note

In Teradata to log information about using tables within query you need to enabled query logging with usecount option for specific log rule. You can find out how to use it in teradata documentation.

Example

Example below enable query logging with use count for every user that logs on.

BEGIN QUERY LOGGING WITH USECOUNT ON ALL;

Query

SELECT  DatabaseName,
        TableName,
        AccessCount,
        LastAccessTimeStamp as LastAccess
FROM    DBC.TablesV
WHERE   DatabaseName NOT IN ('All', 'Crashdumps', 'DBC', 'dbcmngr', 
        'Default', 'External_AP', 'EXTUSER', 'LockLogShredder', 'PUBLIC',
        'Sys_Calendar', 'SysAdmin', 'SYSBAR', 'SYSJDBC', 'SYSLIB', 
        'SystemFe', 'SYSUDTLIB', 'SYSUIF', 'TD_SERVER_DB',  'TDStats',
        'TD_SYSGPL', 'TD_SYSXML', 'TDMaps', 'TDPUSER', 'TDQCD',
        'tdwm',  'SQLJ', 'TD_SYSFNLIB',  'SYSSPATIAL')
AND LastAccessTimeStamp IS NOT NULL
ORDER BY    LastAccessTimeStamp DESC;

Columns

  • DatabaseName - name of the database
  • TableName - name of the table
  • AccessCount - number of accesses to table from query logging start
  • LastAccess - last access time to table

Rows

  • One row represents one table in database
  • Scope of rows: all tables defined in query logging rule in database
  • Ordered by last access time

Sample results

sample results

0
There are no comments. Click here to write the first comment.