Getting Started with Adobe After Effects - Part 6: Motion Blur
A collection of quick technology learning tips from what people around you learn every day

SQL Server - Finding last accessed time for tables by queries or find unused tables

Jan 14 2012 9:26PM by vanne040   

DBA should keep a list of tables which are not being used / referenced by anyone in last few weeks. At times, we come up with a requirement where developer asks, can I have list of unused tables or “Can you tell me the tables which are not being used in last one month”

Script to find out When the table was last accessed since the SQL Services were running

<pre class="brush: plain">
WITH LastActivity (ObjectID, LastAction)
AS
(
SELECT object_id AS TableName, Last_User_Seek as LastAction
FROM sys.dm_db_index_usage_stats 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 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 u 
WHERE database_id = db_id(db_name())
)
SELECT OBJECT_NAME(so.object_id)AS TableName,
MAX(la.LastAction)as LastSelect
FROM
sys.objects so
LEFT JOIN LastActivity la
ON so.object_id = la.ObjectID
WHERE so.type = 'U'
AND so.object_id &gt; 100   --returns only the user tables.Tables with objectid&lt;100 are systables.
GROUP BY OBJECT_NAME(so.object_id)
ORDER BY OBJECT_NAME(so.object_id)
</pre>
Read More..   [167 clicks]

Published under: SQL Server Tips ·  ·  ·  · 


vanne040
84 · 2% · 657
20
 
3
 
18
 
0
Incorrect
 
0
Interesting
 
0
Forgotten



Submit

7  Comments  

  • If on a table, there is no index(Generally for small tables, index is not required), If it is referenced, will it have a row in "sys.dm_db_index_usage_stats"??

    In that case, even when that table is used in query, still it might not show in the DMV..

    commented on Jan 13 2012 11:57PM
    Ramireddy
    2 · 40% · 12972
  • even heaps are referenced in the dmv sys.dmdbindexusagestats

    commented on Jan 14 2012 11:18AM
    vanne040
    84 · 2% · 657
  • What does this return if the SQL Server service was restarted?

    sys.dm_db_index_usage_stats is reinitialized at every service restart. This can result in your query returning misleading results

    commented on Jan 15 2012 6:30AM
    Marc Jellinek
    95 · 2% · 586
  • When SQL Server restarts, DMV & DMFs are reinitialized so query will not return proper data. We can use this query if we are sure about SQL Server restart.

    commented on Jan 16 2012 2:08AM
    Hardik Doshi
    20 · 9% · 2853
  • this query is useful and will give immediate results when SQL Server has not been restarted for some time..

    commented on Jan 16 2012 9:12AM
    vanne040
    84 · 2% · 657
  • That's very useful, thanks, I was looking for that.

    commented on Jan 19 2012 1:32PM
    Jason Yousef
    156 · 1% · 319
  • If on a table, there is no index but table are used everyday, that types of table displayed but last used date is null. In that case, how can identified uses of that type of tables.

    santosh Srivastava-Ahmedabad

    commented on Feb 8 2012 3:28AM
    Santosh Srivastava
    593 · 0% · 61

Your Comment


Sign Up or Login to post a comment.

"SQL Server - Finding last accessed time for tables by queries or find unused tables" rated 5 out of 5 by 20 readers
SQL Server - Finding last accessed time for tables by queries or find unused tables , 5.0 out of 5 based on 20 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]