Clay Lenhart’s Blog

It can be difficult to determine which databases you are administering are actually being used. Below is a query that determines the last time an index was used since the last time SQL Server started to help narrow down these databases:

 
SELECT db.name, i.name, S.*, i.*
 FROM sys.dm_db_Index_Usage_Stats S
      INNER JOIN sys.DATABASES db ON db.database_id = s.database_id
      INNER JOIN sys.Indexes I
              ON S.Index_Id = I.Index_Id
              AND S.Object_Id = I.Object_Id
              AND S.DataBase_Id = DB_ID()
WHERE I.Object_Id > 1000
ORDER BY db.name