Find tables not accessed for past n months in Snowflake

Bart Gawrych - Dataedo Team Bart Gawrych 2019-04-30

Table of Contents:


    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.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy (updated 28-04-2020).
    Accept