Instantly Find Size of Tables on Disk

Here's a script to tell you how big your table(s) are, subtotaled by indexes, with a grand total. Includes LOBs, and excludes dropped pages.

SELECT CASE WHEN GROUPING(t.name) = 0 THEN t.name ELSE 'GRAND TOTAL' END    AS 'Table Name'
     , CASE WHEN GROUPING(i.name) = 0 THEN i.name ELSE 'TABLE TOTAL' END    AS 'Index Name'
     , SUM(au.total_pages) * 8 / 1024                                       AS 'Allocated MB'
     , SUM(au.used_pages)  * 8 / 1024                                       AS 'Used MB'

  FROM sys.allocation_units   au

  LEFT JOIN sys.partitions    pHobt
    ON au.container_id = pHobt.hobt_id
   AND (au.type_desc = 'IN_ROW_DATA'  OR  au.type_desc = 'ROW_OVERFLOW_DATA')

  LEFT JOIN sys.partitions    pLob
    ON au.container_id = pLob.partition_id
   AND au.type_desc    = 'LOB_DATA'

  JOIN sys.indexes            i  
    ON (i.object_id = pHobt.object_id  AND  i.index_id = pHobt.index_id)
         OR
       (i.object_id = pLob.object_id   AND  i.index_id = pLob.index_id)
   
  JOIN sys.tables             t
    ON i.object_id  = t.object_id
  
 WHERE au.type_desc != 'DROPPED'
   AND t.type_desc   = 'USER_TABLE'
   AND COALESCE(pHobt.rows, pLob.rows, 0) > 0
   AND t.name LIKE '%MyTable%'
  
 GROUP BY ROLLUP(t.name, i.name)
 ORDER BY t.name, i.name