Here you go

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

GO

SELECTs.name

, OBJECT_NAME(o.OBJECT_ID)AS TableName

, ISNULL(i.name, 'HEAP')AS IndexName

, i.index_idAS IndexID

, CASE i.[type]

WHEN 0 THEN 'HEAP'

WHEN 1 THEN 'Clustered'

WHEN 2 THEN 'NonClustered'

WHEN 3 THEN 'XML'

WHEN 4 THEN 'Spatial'

ENDAS IndexType

, i.is_disabledAS IsDisabled

, i.data_space_id

, CASE

WHEN i.data_space_id > 65600 THEN ps.name

ELSE f.name

ENDAS FGName

, p.partition_numberAS PartitionNo

, p.[rows]AS [RowCnt]

, p.data_compression_descAS CompressionType

, au.type_descAS AllocType

, au.total_pages / 128AS TotalMBs

, au.used_pages/ 128AS UsedMBs

, au.data_pages/ 128AS DataMBs

FROM sys.indexes i

LEFT OUTER JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id

LEFT OUTER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id

INNER JOIN sys.objects o ON i.object_id = o.object_id

INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id

INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

INNER JOIN sys.allocation_units au ON

CASE

WHEN au.[type] IN (1,3) THEN p.hobt_id

WHEN au.[type] = 2 THEN p.partition_id

END = au.container_id

WHERE o.is_ms_shipped <> 1

ORDER BY TotalMBs DESC