sys.dm_db_index_usage_stats and sys.indexes
use dbdocs; --Replace this with your DBNAME select db_name(dius.database_id) as database_name, dius.database_id, object_name(dius.object_id, dius.database_id) as table_name, dius.object_id as table_id, i.name as index_name, dius.index_id, i.type_desc as index_type, i.is_unique, dius.user_seeks, dius.user_scans, dius.user_lookups, dius.user_seeks + dius.user_scans + dius.user_lookups as user_reads, dius.user_updates as user_writes, dius.last_user_seek, dius.last_user_scan, dius.last_user_lookup, dius.last_user_update from sys.dm_db_index_usage_stats dius inner join sys.indexes i on dius.object_id = i.object_id and dius.index_id = i.index_id where dius.database_id = db_id() and i.index_id <> 0 order by table_name, index_name;
Column | Description ---------------- | ------------------------------------------------------------ database_name | Database name. database_id | ID of the database. table_name | Table name. table_id | ID of the table. index_name | Index name. index_id | ID of the index. index_type | Type of index, CLUSTERED or NONCLUSTERED. is_unique | Is the index unique (1) or not (0). user_seeks | Number of seeks by user queries. user_scans | Number of leaf node scans by user queries. user_lookups | Number of clustered index bookmark lookups by user queries. user_reads | Sum of user_seeks, user_scans, and user_lookups for a total number of reads. user_writes | Number of updates by user queries. last_user_seek | Time of last user seek. last_user_scan | Time of last user scan. last_user_lookup | Time of last user lookup. last_user_update | Time of last user update.