SELECT s.name AS schema_name, o.name AS object_name, i.name AS index_name, i.index_id,
            au.type_desc AS alloc_type, p.rows,
            au.total_pages * 8192 / 1000000 AS total_MB,
            au.used_pages * 8192 / 1000000 AS used_MB
FROM   sys.schemas s
JOIN   sys.objects o ON s.schema_id = o.schema_id
JOIN   sys.indexes i ON o.object_id = i.object_id
JOIN   sys.partitions p ON i.object_id = p.object_id
                       AND i.index_id = p.index_id
JOIN   sys.allocation_units au ON au.container_id = 
                                       CASE WHEN au.type IN (1, 3) THEN p.hobt_id
                                             WHEN au.type = 2 THEN p.partition_id
                                       END
ORDER BY total_MB DESC


; WITH CTE AS (
   SELECT s.name AS schema_name, o.name AS object_name, i.index_id,
            au.type_desc AS alloc_type, p.rows,
            au.total_pages * 8192 / 1000000 AS total_MB,
            au.used_pages * 8192 / 1000000 AS used_MB
   FROM   sys.schemas s
   JOIN   sys.objects o ON s.schema_id = o.schema_id
   JOIN   sys.indexes i ON o.object_id = i.object_id
   JOIN   sys.partitions p ON i.object_id = p.object_id
                           AND i.index_id = p.index_id
   JOIN   sys.allocation_units au ON au.container_id = 
                                          CASE WHEN au.type IN (1, 3) THEN p.hobt_id
                                               WHEN au.type = 2 THEN p.partition_id
                                          END
)
SELECT schema_name, object_name, COUNT(DISTINCT index_id) AS Antalndex,
       MAX(rows) AS no_of_rows, SUM(total_MB) AS total_MB, SUM(used_MB) AS used_MB,
       SUM(CASE WHEN alloc_type <> 'LOB_DATA' THEN total_MB END) AS size_sans_LOB,
       SUM(CASE WHEN alloc_type = 'LOB_DATA' THEN total_MB END) AS size_LOB
FROM   CTE 
GROUP  BY schema_name, object_name
ORDER BY total_MB DESC


; WITH CTE AS (
   SELECT s.name AS schema_name, o.name AS object_name, p.rows,
          (SELECT COUNT(*) FROM sys.columns c WHERE c.object_id = o.object_id) AS colcnt,
          CASE WHEN au.type_desc <> 'LOB_DATA' THEN au.total_pages ELSE 0 END * 8192 / 1000000 AS regular_MB, 
          CASE WHEN au.type_desc = 'LOB_DATA' THEN  au.total_pages ELSE 0 END * 8192 / 1000000 AS LOB_MB,
          CASE WHEN au.type_desc <> 'LOB_DATA' THEN au.used_pages  ELSE 0 END * 8192 / 1000000 AS used_regular_MB, 
          CASE WHEN au.type_desc = 'LOB_DATA' THEN au.used_pages   ELSE 0 END * 8192 / 1000000 AS used_LOB_MB
   FROM   sys.schemas s
   JOIN   sys.objects o ON s.schema_id = o.schema_id
   JOIN   sys.indexes i ON o.object_id = i.object_id
   JOIN   sys.partitions p ON i.object_id = p.object_id
                           AND i.index_id = p.index_id
   JOIN   sys.allocation_units au ON au.container_id = 
                                          CASE WHEN au.type IN (1, 3) THEN p.hobt_id
                                               WHEN au.type = 2 THEN p.partition_id
                                          END
   WHERE i.index_id IN (0, 1)
)
SELECT schema_name, object_name, MAX(colcnt) AS no_of_columns, MAX(rows) AS no_of_rows, 
       SUM(regular_MB + LOB_MB) AS total_MB, SUM(used_regular_MB + used_LOB_MB) AS used_MB,
       SUM(regular_MB) AS regular_MB, SUM(LOB_MB) AS LOB_MB, SUM(SUM(regular_MB)) OVER ()
FROM   CTE 
GROUP  BY schema_name, object_name
ORDER BY total_MB DESC, no_of_rows DESC