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

, iu.user_seeks

, iu.user_scans

, iu.user_updates

, iu.system_seeks

, iu.system_scans

, iu.system_updates

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

inner join sys.dm_db_index_usage_stats iu

      on ((iu.index_id = idx.index_id) and (iu.object_id = idx.object_id))

where ds.name = 'PRIMARY'

order by p1.rows desc