user-tables space usage summary

-- user-tables space usage summary

DBCC UPDATEUSAGE(0)

SELECT o.id, o.name, i.rows
 INTO #rows
 FROM sysobjects o
   INNER JOIN sysindexes i ON o.id = i.id
 WHERE o.type = 'U'
   AND i.indid < 2

SELECT o.id, SUM(i.reserved) reserved
 INTO #reserved
 FROM sysobjects o
   INNER JOIN sysindexes i ON o.id = i.id
 WHERE o.type = 'U'
   AND i.indid IN (0, 1, 255)
 GROUP BY o.id

SELECT A.id, (A.data1 + ISNULL(data2, 0)) data
 INTO #data
 FROM (
   SELECT o.id, SUM(dpages) data1
    FROM sysobjects o
      INNER JOIN sysindexes i ON o.id = i.id
    WHERE o.type = 'U'
      AND indid < 2
    GROUP BY o.id
   ) A
   LEFT JOIN
   (
   SELECT o.id, SUM(used) data2
    FROM sysobjects o
      INNER JOIN sysindexes i ON o.id = i.id
    WHERE o.type = 'U'
      AND indid = 255
    GROUP BY o.id
   ) B
   ON A.id = B.id

SELECT A.id, (A.index_size1 - ISNULL(d.data, 0)) index_size
 INTO #index_size
 FROM (
   SELECT o.id, SUM(used) index_size1
    FROM sysobjects o
      INNER JOIN sysindexes i ON o.id = i.id
    WHERE o.type = 'U'
      AND indid IN (0, 1, 255)
    GROUP BY o.id
   ) A
   LEFT JOIN #data d ON A.id = d.id

SELECT r.id, (r.reserved - ISNULL(B.ununsed2, 0)) unused
 INTO #unused
 FROM #reserved r
   LEFT JOIN
   (
   SELECT o.id, SUM(used) ununsed2
    FROM sysobjects o
      INNER JOIN sysindexes i ON o.id = i.id
    WHERE o.type = 'U'
      AND indid in (0, 1, 255)
    GROUP BY o.id
   ) B ON r.id = B.id

SELECT r.name
  , r.rows
  , (v.reserved * 8) 'reserved (KB)'
  , (d.data * 8) 'data (KB)'
  , (i.index_size * 8) 'index_size (KB)'
  , (u.unused * 8) 'unused (KB)'
 FROM #rows r
   INNER JOIN #reserved v   ON r.id = v.id
   INNER JOIN #data d   ON r.id = d.id
   INNER JOIN #index_size i ON r.id = i.id
   INNER JOIN #unused u  ON r.id = u.id
 ORDER BY d.data + i.index_size DESC
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s