T-SQL Listar todas las tablas de una base de datos con sus respectivos tamaños

   1: SELECT

   2:     X.[name],

   3:     REPLACE(CONVERT(varchar, CONVERT(money, X.[rows]), 1), '.00', '')

   4:         AS [rows],

   5:     REPLACE(CONVERT(varchar, CONVERT(money, X.[reserved]), 1), '.00', '')

   6:         AS [reserved],

   7:     REPLACE(CONVERT(varchar, CONVERT(money, X.[data]), 1), '.00', '')

   8:         AS [data],

   9:     REPLACE(CONVERT(varchar, CONVERT(money, X.[index_size]), 1), '.00', '')

  10:         AS [index_size],

  11:     REPLACE(CONVERT(varchar, CONVERT(money, X.[unused]), 1), '.00', '')

  12:         AS [unused]

  13: FROM

  14: (SELECT

  15:     CAST(object_name(id) AS varchar(50))

  16:         AS [name],

  17:     SUM(CASE WHEN indid < 2 THEN CONVERT(bigint, [rows]) END)

  18:         AS [rows],

  19:     SUM(CONVERT(bigint, reserved)) * 8

  20:         AS reserved,

  21:     SUM(CONVERT(bigint, dpages)) * 8

  22:         AS data,

  23:     SUM(CONVERT(bigint, used) - CONVERT(bigint, dpages)) * 8

  24:         AS index_size,

  25:     SUM(CONVERT(bigint, reserved) - CONVERT(bigint, used)) * 8

  26:         AS unused

  27:     FROM sysindexes WITH (NOLOCK)

  28:     WHERE sysindexes.indid IN (0, 1, 255)

  29:         AND sysindexes.id > 100

  30:         AND object_name(sysindexes.id) <> 'dtproperties'

  31:     GROUP BY sysindexes.id WITH ROLLUP

  32: ) AS X

  33: WHERE X.[name] is not null

  34: ORDER BY X.[rows] DESC

El Blog de JuanBO