Viewing the disk space usage (size) of all the tables in a SQL Server 2005/2008 database

I spent a couple of hours searching for a script that returns the size of all the tables in a database. Most of the ones that you find use sp_msforeachtable but that is just for one table and you then need to use temporary tables to hold the values.
 
This article on the Database Journal by Ananth Kumar (AKA MAK) had this handy script that simply works by copy-pasting it as is. Read the linked article for details on viewing the size by schema.
 
BEGIN TRY 
SELECT 
    (row_number() over(order by a3.name, a2.name))%2 as l1,
    a3.name AS [schemaname],
    a2.name AS [tablename],
    a1.rows as row_count,
    (a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved, 
    a1.data * 8 AS data,
    (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,
    (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
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 <> 'S' and a2.type <> 'IT'
ORDER BY a3.name, a2.name
END TRY 
BEGIN CATCH 
SELECT 
 -100 as l1
, 1 as schemaname 
,       ERROR_NUMBER() as tablename
,       ERROR_SEVERITY() as row_count
,       ERROR_STATE() as reserved
,       ERROR_MESSAGE() as data
,       1 as index_size
,   1 as unused 
END CATCH

 

Quick extra tip, if you export the result-set to Excel you can do additional sorts to figure out more stuff. For example the first table on this list was taking up 47GB of disk space in a 63 GB database. With the index taking more space than the actual data! But this is more of an OLAP db with just one batch update daily and optimized for retrieving data faster so I guess that’s okay.

sql-disk-space-usage

 

Viewing the disk space usage (size) of all the tables in a SQL Server 2005/2008 database
  • Priyakara

    Chk this.

    CREATE TABLE #temp (TableName NVARCHAR (128), RowsCnt VARCHAR (11), ReservedSpace VARCHAR(18), DataSpace VARCHAR(18), CombinedIndexSpace VARCHAR(18), UnusedSpace VARCHAR(18))
    EXEC sp_MSforeachtable ‘INSERT INTO #temp (TableName, RowsCnt, ReservedSpace, DataSpace, CombinedIndexSpace, UnusedSpace) EXEC sp_spaceused ”?”, FALSE’
    SELECT TableName, RowsCnt, ReservedSpace, DataSpace, CombinedIndexSpace, UnusedSpace
    FROM #temp
    ORDER BY TableName
    DROP TABLE #temp

  • raj

    Hi

    we got this error from ur code…

    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword ‘AS’.
    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword ‘AS’.
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ‘END’.