Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 14 Aug 2015 @ 16:35:46 GMT


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


Subj:   List of tables not accessed in 6 months
 
From:   Bakthavachalam, Roopalini

Forum - I posted a question earlier on accesscount and lastaccesstimestamp of tables being null. What actually I am trying to find is the list of tables not being accessed more than 6 months in a particular database. We do have ObjectUseCountCollectRate enabled. I came up with the below query , however it's not giving me the right results. I am sure there is a more elegant way to do this. Appreciate your help greatly.

     SELECT
     CAST(DATABASENAME AS VARCHAR(128)),
     CAST(TABLENAME AS VARCHAR(128))
     FROM
     DBC.TABLES
     WHERE DATABASENAME IN ('ABC')
     MINUS

     SELECT
     OBJDB_NAME,
     OBJNAME
     FROM
     (
         SELECT
         B.OBJECTDATABASENAME AS OBJDB_NAME,
         B.OBJECTTABLENAME AS OBJNAME
          FROM PDCRDATA.DBQLOGTBL_HST A
         INNER JOIN
         (SELECT *  FROM PDCRDATA.DBQLOBJTBL_HST) B
          ON A.QUERYID = B.QUERYID
          AND A.LOGDATE = B.LOGDATE

     WHERE
         A.LOGDATE >= 1150201 AND
         B.OBJECTDATABASENAME IN ('ABC')
         AND B.OBJECTTABLENAME IS NOT NULL
         GROUP BY 1,2
     )A

Thanks

Roopalini



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016