SELECT p1.object_id

, object_name(p1.Object_id) Objectname

, p1.Partition_number

, ds.name as data_space_name

, p1.rows as rows_in_data_space

, p1.index_id

, idx.name index_name

, idx.type_desc index_type

, idx.is_unique index_is_unique

FROM sys.partitions p1

inner join sys.allocation_units au

      on p1.hobt_id = au.container_id

inner join sys.data_spaces ds

      on au.data_space_id = ds.data_space_id

inner join sys.indexes idx

      on idx.index_id = p1.index_id

      and idx.object_id = p1.object_id

order by ds.name, object_name(p1.Object_id), idx.name