SELECT top 10 @@ServerName AS ServerName,
        db_name() AS [DATABASE],
        object_schema_name(t.object_id) AS [schema],
        --object_name(t.object_id) as [name],
       (SELECT COUNT(1) FROM sys.columns AS c WHERE c.object_id = t.object_id) AS [COLUMNS],
       (SELECT name 
          FROM sys.database_principals 
         WHERE principal_id = isnull(t.principal_id,(SELECT principal_id 
                                                       FROM sys.schemas AS s 
                                                      WHERE s.schema_id = t.schema_id))
       ) AS [owner],
        t.*,
        s.*
   FROM sys.tables AS t
   JOIN (SELECT object_id,
              --count(partition_id)
                COUNT(DISTINCT index_id)                        AS  indexes,
                MAX(CASE WHEN index_id = 0 THEN 'Heap' 
                         WHEN index_id = 1 THEN 'Clustered'
                         ELSE ''
                     END) AS HOBT,
                MAX(partition_number)                           AS  partitions,
                SUM(in_row_data_page_count)*8192                AS  in_row_data_page_bytes,
                SUM(in_row_used_page_count)*8192                AS  in_row_used_page_bytes,
                SUM(in_row_reserved_page_count)*8192            AS  in_row_reserved_page_bytes,
                SUM(lob_used_page_count)*8192                   AS  lob_used_page_bytes,
                SUM(lob_reserved_page_count)*8192               AS  lob_reserved_page_bytes,
                SUM(row_overflow_used_page_count)*8192          AS  row_overflow_used_page_bytes,
                SUM(row_overflow_reserved_page_count)*8192      AS  row_overflow_reserved_page_bytes,
                SUM(used_page_count)*8192                       AS  used_page_bytes,
                SUM(reserved_page_count)*8192                   AS  reserved_page_bytes,
                SUM(CASE WHEN index_id < 2 THEN ROW_COUNT ELSE 0 END) AS  ROWS
                FROM sys.dm_db_partition_stats AS x
          GROUP BY object_id 
        ) AS s
      ON s.object_id = t.object_id 
   ORDER BY used_page_bytes DESC, [schema],[name]