Find tables not accessed for past n months in Snowflake

Query below returns list of tables that was not modified in the last n month.

Query

select table_schema,
       table_name,
       last_altered
from information_schema.tables
where table_type = 'BASE TABLE'
      and last_altered < dateadd( 'MONTH', -2, current_timestamp() ) 
order by table_schema,
         table_name;

Columns

  • table_schema - name of the table schema
  • table_name - name of the table
  • last_altered - datetime of last access which modified table

Rows

  • One row represents one table in database
  • Scope of rows: all tables not accessed for past 2 months in database
  • Ordered by table schema and table name

Sample results

sample results

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