I'm archiving data from an old system that has no documentation. Lucky me…
I'd like to find out when the tables were created, last accessed etc. Can I trust that this query will give me the correct answer or is there some parameters that I first need to check? SQL Server 2008 R2:
SELECT t.Name AS Tabelname, p.rows AS NoOfRows, MAX(us.last_user_lookup) AS LastUsed, t.create_date AS CreatedDate
FROM sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
LEFT JOIN --A lot of the tables did not have any records in this table
sys.dm_db_index_usage_stats as us ON t.OBJECT_ID = us.OBJECT_ID
GROUP BY t.Name, p.rows, create_date
ORDER BY MAX(us.last_user_lookup) DESC
Best Answer
No.
The
sys.dm_db_index_usage_stat
view reflects only, at best, data since the last database startup (last instance startup, or last time the DB was brought online). Furthermore the entries may clear under memory pressure. It will give accurate positives (if a table has non-zero stats then it is used)) but may give false negatives (0 usage in stats may not reflect actual usage). Also there are many systems that use certain tables only once per week, once per month or even once per year.