Last read / write databases

WITH vins AS
(
   SELECT
       max(last_user_seek) last_user_seek,
       max(last_user_scan) last_user_scan,
       max(last_user_lookup) last_user_lookup,
       max(last_user_update) last_user_update,
       sd.name dbname
   FROM
       sys.dm_db_index_usage_stats, master..sysdatabases sd
   WHERE
     database_id = sd.dbid  group by sd.name
)
SELECT
   dbname,
   last_read = MAX(last_read),
   last_write = MAX(last_write)
FROM
(
   SELECT dbname, last_user_seek, NULL FROM vins
   UNION ALL
   SELECT dbname, last_user_scan, NULL FROM vins
   UNION ALL
   SELECT dbname, last_user_lookup, NULL FROM vins
   UNION ALL
   SELECT dbname, NULL, last_user_update FROM vins
) AS x (dbname, last_read, last_write)
GROUP BY
   dbname
ORDER BY 2;

This site uses Akismet to reduce spam. Learn how your comment data is processed.