I need SQL to extract all tables in a database whose secondary indexes have not been accessed in the last one year. Can someone provide SQL to check the secondary indexes and their last accesstimestamp.
Thanks in advance
If objectusage is enabled, you may try something like below,
select a.databaseid,a.objectid,a.fieldid,a.useraccesscnt,b.indextype,a.lastaccesstimestamp from dbc.objectusage a join dbc.indexes b on a.databaseid=b.databaseid and a.objectid=b.tableid where b.indextype='S' and a.lastaccesstimestamp < date-360 and a.useraccesscnt < 3 ;
Map above query result with tvm and tvfields to determine the tablename and fieldname.
GANGA SANDEEP KUMAR