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