Find tables not accessed for past n months in Snowflake

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

Table of Contents:


    Article for: Snowflake SQL Server IBM Db2

    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.