declare @usage table (
    object_id int,
    schema_name sysname,
    table_name sysname,
    index_name sysname,
    index_id int,
    usedKB int,
    reservedKB int,
    row_count bigint,
    primary key clustered ( object_id, index_id )
    )
insert into @usage
    select
        p.object_id,
        s.name,
        o.name,
        coalesce( i.name, 'HEAP' ),
        i.index_id,
        p.used_page_count * 8,
        p.reserved_page_count * 8,
        p.row_count
        from sys.dm_db_partition_stats p
        join sys.objects o on p.object_id = o.object_id
        join sys.schemas s on o.schema_id = s.schema_id
        left join sys.indexes i on o.object_id = i.object_id and p.index_id = i.index_id
        where o.type_desc = 'USER_TABLE' and o.is_ms_shipped = 0

--ByIndex
select *
    from @usage
    order by schema_name, table_name, index_name, index_id

--ByTable
select
    schema_name, table_name,
    usedKB = sum(usedKB), reservedKB = sum(reservedKB), rows = max(row_count)
    from @usage
    group by schema_name, table_name
    order by schema_name, table_name