Archives of the TeradataForum

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

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

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>>