Last Time Tables Used - Referenced

http://beyondrelational.com/modules/1/justlearned/388/tips/9111/sql-server-finding-last-accessed-time-for-tables-by-queries-or-find-unused-tables.aspx?utm_source=brnewsletter&utm_medium=email&utm_campaign=2012Feb


Requires 8.0 compatibility.

--select su.[admin] from sys_users su where su.username like 'kellyjoe%'

-- update su set su.[admin] = 0 from sys_users su where su.username like 'kellyjoe%'
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 > 100 --returns only the user tables.Tables with objectid<100 are systables.
GROUP BY OBJECT_NAME(so.object_id)
ORDER BY OBJECT_NAME(so.object_id)