List of tables altered in last 10 days

Post date: Oct 12, 2014 8:10:59 PM

SELECT

n.nspname, 

c.relname,

a.statime

FROM

pg_class c INNER JOIN pg_namespace n

ON (c.relnamespace = n.oid)

LEFT OUTER JOIN pg_partitions pgp

ON (

pgp.schemaname = n.nspname

AND pgp.tablename = c.relname

)

LEFT OUTER JOIN (

SELECT DISTINCT

objid,

statime

FROM

pg_stat_last_operation

WHERE

staactionname in ('ALTER')

) a

ON (a.objid = c.oid)

WHERE

n.nspname NOT IN ('pg_catalog','information_schema','gp_toolkit')

and c.relname not like 'stg%' and c.relname not like 'swap%'

AND relkind in ('r')

AND c.relstorage = 'h'

and date_trunc('day',statime) >= date_trunc('day',localtimestamp- interval '10 day')

ORDER BY reltuples DESC;