SELECT
    sch.name                             AS SchemaName,
    t.name                               AS TableName,
    i.name                               AS IndexName,
    i.type_desc                          AS IndexType,

    -- 📦 Розмір індексу
    SUM(ps.used_page_count) * 8 / 1024.0 AS IndexSizeMB,

    -- 📊 Використання
    ISNULL(us.user_seeks,   0) AS UserSeeks,
    ISNULL(us.user_scans,   0) AS UserScans,
    ISNULL(us.user_lookups, 0) AS UserLookups,
    ISNULL(us.user_updates, 0) AS UserUpdates,

    -- 🕒 Останнє використання
    us.last_user_seek,
    us.last_user_scan,
    us.last_user_lookup,
    us.last_user_update

FROM sys.tables t
JOIN sys.schemas sch
    ON sch.schema_id = t.schema_id
JOIN sys.indexes i
    ON i.object_id = t.object_id
JOIN sys.dm_db_partition_stats ps
    ON ps.object_id = i.object_id
   AND ps.index_id  = i.index_id
LEFT JOIN sys.dm_db_index_usage_stats us
    ON us.object_id   = i.object_id
   AND us.index_id    = i.index_id
   AND us.database_id = DB_ID()

WHERE t.name = 'YourTableName'   -- 👈 назва таблиці
  AND i.index_id > 0             -- без heap-запису

GROUP BY
    sch.name,
    t.name,
    i.name,
    i.type_desc,
    us.user_seeks,
    us.user_scans,
    us.user_lookups,
    us.user_updates,
    us.last_user_seek,
    us.last_user_scan,
    us.last_user_lookup,
    us.last_user_update

ORDER BY IndexSizeMB DESC;