Find tables not accessed for past n months in SQL Server database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-04-01

Table of Contents:


    Query below returns list of tables that was not accessed in the last 3 month.

    Note

    In SQL Server you can find out when table was last accessed by quering dm_db_index_usage_stats view, but note that this view is cleaned each time SQL Server is restarted.

    Query

    select [schema_name],
           table_name,
           max(last_access) as last_access
    from(
        select schema_name(schema_id) as schema_name,
               name as table_name,
               (select max(last_access) 
                from (values(last_user_seek),
                            (last_user_scan),
                            (last_user_lookup), 
                            (last_user_update)) as tmp(last_access))
                    as last_access
    from sys.dm_db_index_usage_stats sta
    join sys.objects obj
         on obj.object_id = sta.object_id
         and obj.type = 'U'
         and sta.database_id = DB_ID()
    ) usage
    where last_access < dateadd(month, -3, current_timestamp)
    group by schema_name,
             table_name
    order by last_access desc;
    

    Columns

    • schema_name - name of the database
    • table_name - name of the table
    • last_access - datetime of last access

    Rows

    • One row represents one table in database
    • Scope of rows: all tables not accessed for past 3 months in database
    • Ordered by last access time descending

    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