Get details of indexes

Get-IndexDetail

SELECT T.DatabaseName,
       T.ObjectId,
       T.ObjectName,
       T.IndexId,
       T.IndexDescription,
       CONVERT(DECIMAL(16, 1), (SUM(T.avg_record_size_in_bytes * T.record_count) / (1024.0 * 1024))) AS [IndexSize(MB)],
       T.lastupdated AS [StatisticLastUpdated],
       T.AvgFragmentationInPercent
FROM
(
    SELECT DISTINCT
           DB_NAME(database_id) AS DatabaseName,
           object_id AS ObjectId,
           OBJECT_NAME(object_id) AS ObjectName,
           index_id AS IndexId,
           index_type_desc AS IndexDescription,
           avg_record_size_in_bytes,
           record_count,
           STATS_DATE(object_id, index_id) AS lastupdated,
           CONVERT([VARCHAR](512), ROUND(avg_fragmentation_in_percent, 3)) AS AvgFragmentationInPercent
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'detailed')
    WHERE object_id IS NOT NULL
          AND avg_fragmentation_in_percent <> 0
) AS T
GROUP BY T.DatabaseName,
         T.ObjectId,
         T.ObjectName,
         T.IndexId,
         T.IndexDescription,
         T.lastupdated,
         T.AvgFragmentationInPercent;