Wednesday, February 8, 2012

Script: View when the tables in a database were accessed last

I had to do a database clean up project, where I had to drop the usused table. I ran the following script to see when were the indexes used last and made the decision based on it. One caution though was the table that did not have indexes did not show the activity.

use [yourdb]
GO

SELECT
sobj.[name],iu.last_user_seek,101,iu.last_user_update  FROM sys.dm_db_index_usage_stats iu
INNER JOIN sys.objects sobj
ON iu.object_id = so.object_id
WHERE so.type_desc = 'USER_Table'
ORDER BY  so.[name],last_user_seek
GO

No comments:

Post a Comment