Mini Post: Get Sizes Of Tables In A Database

Hey all, here's something I've been meaning to put up for a while.

Its a very common question that pops up from time to time so I thought I'd share the answer in one more place on the internet.

Well two answers actually: The Report Solution and The Query Solution.

The Report Solution

Be honest. How often do you use the built in reports? How many of you even know they're there?

Simply right click the apprpriate database, go to Reports -> Standard Reports -> Disk Usage By Table.

Fig. 1: SQL Server Standard Reports. Note other useful reports.

You will then be presented with a report like the below:

Fig. 2: Report results.

The Query Solution

Found the original query here.

I made tiny changes to the column names so you can see that it presents the same info found in the report solution:

SELECT
    t.NAME AS 'Table Name',
    p.rows AS '#Records',
    SUM(a.total_pages) * 8 AS 'Reserved (KB)',
    SUM(a.used_pages) * 8 AS 'Used (KB): Data + Index', 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS 'Unused (KB)'
FROM
    sys.tables t
INNER JOIN     
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, p.Rows
ORDER BY
    t.name
   
The benefit of the query approach is that you can tweak it a bit to suit your needs, eg order it by table size instead of table name.


 DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)