Como obtener el tamaño de las todas las tablas de una base de datos SQL Server

Muchas veces, el tamaño de una base de datos se nos dispara y no sabemos muy bien porqué. Suele ocurrir por tablas de traza principalmente, pero a veces es difícil saber exactamente donde está el "problema".

Hay varias opciones para obtener el tamaño de las tablas de una base de datos SQL Server, pero aquí os voy a poner el que más me gusta a mi, porque es un script que se ejecuta en la base de datos que queremos analizar:

SELECT a3.name + '.' + a2.name AS [name], 
   a1.rows AS [rows], 
   (a1.reserved + ISNULL(a4.reserved, 0)) * 8 AS [Reserved KB], 
   a1.data * 8 AS [Data KB], 
   (CASE
        WHEN(a1.used + ISNULL(a4.used, 0)) > a1.data
        THEN(a1.used + ISNULL(a4.used, 0)) - a1.data
        ELSE 0
    END) * 8 AS [Index_size KB], 
   (CASE
        WHEN(a1.reserved + ISNULL(a4.reserved, 0)) > a1.used
        THEN(a1.reserved + ISNULL(a4.reserved, 0)) - a1.used
        ELSE 0
    END) * 8 AS [unused KB], 
   CONVERT(DECIMAL(18, 2), (((a1.reserved + ISNULL(a4.reserved, 0)) * 8) - ((CASE
                                                                                 WHEN(a1.reserved + ISNULL(a4.reserved, 0)) > a1.used
                                                                                 THEN(a1.reserved + ISNULL(a4.reserved, 0)) - a1.used
                                                                                 ELSE 0
                                                                             END) * 8)) / 1024.0 / 1024.0) AS [Table_used_Space GB]
FROM
(
SELECT ps.object_id, 
       SUM(CASE
               WHEN(ps.index_id < 2)
               THEN row_count
               ELSE 0
           END) AS [rows], 
       SUM(ps.reserved_page_count) AS reserved, 
       SUM(CASE
               WHEN(ps.index_id < 2)
               THEN(ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
               ELSE(ps.lob_used_page_count + ps.row_overflow_used_page_count)
           END) AS data, 
       SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id
) AS a1
LEFT OUTER JOIN
(
SELECT it.parent_id, 
       SUM(ps.reserved_page_count) AS reserved, 
       SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
     INNER JOIN sys.internal_tables it ON(it.object_id = ps.object_id)
WHERE it.internal_type IN(202, 204)
GROUP BY it.parent_id
) AS a4 ON(a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON(a1.object_id = a2.object_id)
INNER JOIN sys.schemas a3 ON(a2.schema_id = a3.schema_id)
WHERE a2.type != N'S'
  AND a2.type != N'IT'

ORDER BY [Table_used_Space GB] DESC, [rows] desc ;

El resultado es este:

Este script no es mío. Es de Vivek Janakiraman que lo publicó en las galerías Technet de Microsoft.