Show Last Table Activity

---
-- Show Last Table Activity
--
-- This script will return a list of user tables in the database, along with the
-- last time a SELECT was executed against them (including any views that
-- include data from that table). This can be used to determine if a table is
-- not in use anymore.
--
-- Statistics are reset when the SQL Server sevice is restarted, so this query
-- will only return activity since that time. Also note, just because there's no
-- activity, doesn't mean it's safe to remove an object - some tables may only
-- be used during monthly or annual processes, and so they wouldn't show any
-- activity except during those brief intervals.
--

--
-- method 1:
with LastActivity(ObjectID, LastAction)
as (
    select object_id as TableName, last_user_seek as LastAction
    from sys.dm_db_index_usage_stats as u
    where database_id = db_id(db_name())
    union
    select object_id as TableName, last_user_scan as LastAction
    from sys.dm_db_index_usage_stats as u
    where database_id = db_id(db_name())
    union
    select object_id as TableName, last_user_lookup as LastAction
    from sys.dm_db_index_usage_stats as u
    where database_id = db_id(db_name())
)
    select object_name(so.object_id) as TableName, max(la.LastAction) as LastSelect
    from sys.objects as so
    left join LastActivity as la
        on so.object_id = la.ObjectID
    where so.type = 'U' and so.object_id > 100
    group by object_name(so.object_id)
    order by object_name(so.object_id);

--
-- method 2: (http://stackoverflow.com/a/12097461)
select OBJECT_SCHEMA_NAME(object_id) as [schema],
    OBJECT_NAME(object_id) as [object],
    MAX(LastAccessDate) as LastAccessDate
from (
    select object_id,
        last_user_seek,
        last_user_scan,
        last_user_lookup,
        last_user_update
    from sys.dm_db_index_usage_stats
    where database_id = DB_ID()
    ) as PivotTable unpivot(
            LastAccessDate for last_user_access in(
                last_user_seek,
                last_user_scan,
                last_user_lookup,
                last_user_update
            )
    ) as UnpivotTable
group by object_id
order by LastAccessDate;