SQL : View Table Size

SQL : View Table Size

The following SQL will provide size infomation (in KB) for each table in the local database contex in a single table:

  SET NOCOUNT ON
  DBCC UPDATEUSAGE(0)

  SELECT sysobjects.[name] AS [TableName],
    SUM(sysindexes.reserved) * 8 AS [Size(KB)],
    SUM(sysindexes.dpages) * 8 AS [Data(KB)],
    (SUM(sysindexes.used) – SUM(sysindexes.dpages)) * 8 AS [Indexes(KB)],
    (SUM(sysindexes.reserved) – SUM(sysindexes.dpages)) * 8 AS [Unused(KB)]
  FROM dbo.sysindexes AS sysindexes
    JOIN dbo.sysobjects AS sysobjects ON sysobjects.id = sysindexes.id
  WHERE sysobjects.[type] = ‘U’
  GROUP BY sysobjects.[name]
  ORDER BY [Size(KB)] DESC