Find Unused Indexes

------------------------------------------------------------------------------

------------------------------------------------------------------------------
-- Get all cached plans - parse out statistics used
------------------------------------------------------------------------------
DROP TABLE IF EXISTS #tmpStatsUsed; --SELECT * FROM #tmpStatsUsed;

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS x)
SELECT x.plan_handle, x.TableName, x.SchemaName, x.DatabaseName, x.StatName
INTO #tmpStatsUsed
FROM (
    SELECT cp.plan_handle
        , TableName     = x.n.value('(./@Table)[1]', 'nvarchar(128)')
        , SchemaName    = x.n.value('(./@Schema)[1]', 'nvarchar(128)')
        , DatabaseName  = x.n.value('(./@Database)[1]', 'nvarchar(128)')
        , StatName      = x.n.value('(./@Statistics)[1]', 'nvarchar(128)')
    FROM sys.dm_exec_cached_plans cp
        CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
        CROSS APPLY qp.query_plan.nodes('/x:ShowPlanXML/x:BatchSequence/x:Batch/x:Statements/x:StmtSimple/x:QueryPlan/x:OptimizerStatsUsage/x:StatisticsInfo') x(n)
) x
WHERE x.DatabaseName NOT IN ('[tempdb]','[master]','[msdb]')
    AND x.SchemaName NOT IN ('[sys]')
--  AND x.StatName NOT LIKE '\[\_WA\_Sys\_%' ESCAPE '\';
------------------------------------------------------------------------------

------------------------------------------------------------------------------
-- Get all indexes, usage data, statistics info, etc
------------------------------------------------------------------------------
WITH cte_unique_stats AS (
    SELECT TableName, SchemaName, DatabaseName, StatName
    FROM #tmpStatsUsed
    GROUP BY TableName, SchemaName, DatabaseName, StatName
)
SELECT s.[name]
    , stats_last_updated = sp.last_updated
    , i.[type_desc], i.is_unique, i.is_primary_key, i.is_unique_constraint
    , i.fill_factor, i.is_disabled, i.has_filter, i.filter_definition
    , total_reads = COALESCE(ius.user_seeks,0) + COALESCE(ius.user_scans,0) + COALESCE(ius.user_lookups,0)
    , total_writes = ius.user_updates
    , last_read  = lr.last_read
    , last_write = ius.last_user_update
    , x.DatabaseName, x.SchemaName, x.TableName
FROM sys.stats s
    CROSS APPLY sys.dm_db_stats_properties(s.[object_id], s.stats_id) sp
    LEFT JOIN sys.indexes i ON i.[object_id] = s.[object_id] AND i.[name] = s.[name]
    LEFT JOIN sys.dm_db_index_usage_stats ius ON ius.[object_id] = i.[object_id] AND ius.index_id = i.index_id AND ius.database_id = DB_ID()
    CROSS APPLY (SELECT last_read = MAX(x.last_read) FROM (VALUES (ius.last_user_seek),(ius.last_user_scan),(ius.last_user_lookup)) x(last_read)) lr
    LEFT JOIN cte_unique_stats x ON x.StatName = QUOTENAME(s.[name]) AND x.TableName = QUOTENAME(OBJECT_NAME(s.[object_id])) AND x.DatabaseName = QUOTENAME(DB_NAME())
WHERE OBJECT_NAME(s.[object_id]) = 'SomeTableName'
    AND s.auto_created = 0;
------------------------------------------------------------------------------

------------------------------------------------------------------------------