use dbdocs; --Replace with your Database Name select db_name(dius.database_id) as database_name, object_name(dius.object_id, dius.database_id) as table_name, i.name as index_name, i.type_desc as index_type, dius.user_seeks + dius.user_scans + dius.user_lookups as user_reads, dius.user_updates as user_writes 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 objectproperty(dius.object_id, 'IsUserTable') = 1 and i.index_id <> 0 and dius.user_seeks + dius.user_scans + dius.user_lookups = 0 order by table_name, index_name;
use dbdocs; --Replace with your Database Name select object_name(i.object_id) as table_name, i.name as index_name, i.type_desc as index_type from sys.indexes i inner join sys.objects o on i.object_id = o.object_id where not exists ( select dius.index_id from sys.dm_db_index_usage_stats dius where dius.object_id = i.object_id and dius.index_id = i.index_id and dius.database_id = db_id() ) and o.type = 'U' and i.index_id <> 0 order by table_name, index_name;