sql 2005 - Last Time Table Used in Query
Does SQL store the last date-time a table was part of a query?
I'm trying to determine which tables are not being used and I would like to not bother asking about tables used on a daily basis, but ask about tables not used in months.
I'm trying to determine which tables are not being used and I would like to not bother asking about tables used on a daily basis, but ask about tables not used in months.
Brendt Hess
Not directly. However, some of this can be derived from the standard reports by database. Use the Index Usage Statistics report as a good starting surrogate for this - if there is a table that hasn't had any index usage (assuming the table has an index), it likely has not been touched for the same time. This value is only maintained since the last restart of the SQL Server, though.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Below is code from supplied link. It can get the data needed.
select
t.name
,user_seeks
,user_scans
,user_lookups
,user_updates
,last_user_seek
,last_user_scan
,last_user_lookup
,last_user_update
from
sys.dm_db_index_usage_stat
sys.tables t ON (t.object_id = i.object_id)
where
database_id = db_id()
select
t.name
,user_seeks
,user_scans
,user_lookups
,user_updates
,last_user_seek
,last_user_scan
,last_user_lookup
,last_user_update
from
sys.dm_db_index_usage_stat
sys.tables t ON (t.object_id = i.object_id)
where
database_id = db_id()