Get Table and Index storage size in sql server

Question

I want to get table data and index space for every table in my database:

Table Name             Data Space           Index Space
-------------------------------------------------------

How can I achieve this result?

Solution 1

This query here will list the total size that a table takes up - clustered index, heap and all nonclustered indices:

SELECT 
    s.Name AS SchemaName,
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN 
    sys.schemas s ON s.schema_id = t.schema_id
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%'    -- filter out system tables for diagramming
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    s.Name, t.Name

If you want to separate table space from index space, you need to use AND i.index_id IN (0,1) for the table space (index_id = 0 is the heap space, index_id = 1 is the size of the clustered index = data pages) and AND i.index_id > 1 for the index-only space

Solution 2

with pages as (
    SELECT object_id, SUM (reserved_page_count) as reserved_pages, SUM (used_page_count) as used_pages,
            SUM (case 
                    when (index_id < 2) then (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
                    else lob_used_page_count + row_overflow_used_page_count
                 end) as pages
    FROM sys.dm_db_partition_stats
    group by object_id
), extra as (
    SELECT p.object_id, sum(reserved_page_count) as reserved_pages, sum(used_page_count) as used_pages
    FROM sys.dm_db_partition_stats p, sys.internal_tables it
    WHERE it.internal_type IN (202,204,211,212,213,214,215,216) AND p.object_id = it.object_id
    group by p.object_id
)
SELECT object_schema_name(p.object_id) + '.' + object_name(p.object_id) as TableName, (p.reserved_pages + isnull(e.reserved_pages, 0)) * 8 as reserved_kb,
        pages * 8 as data_kb,
        (CASE WHEN p.used_pages + isnull(e.used_pages, 0) > pages THEN (p.used_pages + isnull(e.used_pages, 0) - pages) ELSE 0 END) * 8 as index_kb,
        (CASE WHEN p.reserved_pages + isnull(e.reserved_pages, 0) > p.used_pages + isnull(e.used_pages, 0) THEN (p.reserved_pages + isnull(e.reserved_pages, 0) - p.used_pages + isnull(e.used_pages, 0)) else 0 end) * 8 as unused_kb
from pages p
left outer join extra e on p.object_id = e.object_id

Takes into account internal tables, such as those used for XML storage.

Edit: If you divide the data_kb and index_kb values by 1024.0, you will get the numbers you see in the GUI.

Comments

[Arian]

thanks dear @marc_s.If we right click on a table name and choose Properties in storage tab there are Data Space and Index Space.If I want to get those numbers what can I do?

[gotqn]

@marc_s There should be something wrong here. Are you usre SUM(a.used_pages) * 8 gives you used space in KB? I thinkg it should be Byte instead. Could you double check this? whatsabyte.com/P1/byteconverter.htm

[marc_s]

@gotqn: the page in SQL Server is always 8 KB - so the number of pages multiplied by 8 gives you the amount in KB that is taken

[gotqn]

@marc_s Thanks for the quick response. I was confused and interpreted wrong the size of a table that SQL Management Studio is showing.

[piers7]

You need to add schema_id into your GROUP BY (and into the select list as SCHEMA_NAME(t.SCHEMA_ID), otherwise you're collapsing sizes across schemas.

[marc_s]

@piers7: true - but in my experience, 99% or more of the databases only ever have the dbo schema ..... but your point is valid, IF you have multiple schemata in your database

[marc_s]

@piers7: updated my SQL statement with the schema information.

[MikeB]

Anyone who uses this code will probably want to remove the "t.NAME NOT LIKE 'dt%'" condition from the WHERE clause. I can't guess why it's there.

[marc_s]

@MikeB: it's there to filter out the various system tables that are installed if you use the database diagramming features of SQL Server

[Pavel Nefyodov]

Number of rows should be removed from GROUP BY clause and RowCounts rewritten as SUM( CASE WHEN (i.index_id=0) OR (i.index_id=1) THEN p.rows ELSE 0 END ) AS RowCounts

[Pavel Nefyodov]

Otherwise this script will show incorrect results. Example: the database has tables with filtered indexes (actual number of rows in index is different from the total number of rows in the table).

[crokusek]

fyi, during a large cascading delete, the query showed multiple rows (4) for a single table. I applied @PavelNefyodov's comment but am not 100% sure the summed row count was correct. Example output was Rows/DataMb/IndexMb: 45, 0, 27; 20, 0, 0; 21, 15, 0; 19, 0, 0;

[user__42]

i use filtered indexes on some tables, so i use MAX(p.rows) AS RowCounts together with GROUP BY t.Name, s.Name

[Paul Evans]

To add a thousands separator, add a FORMAT e.g. FORMAT(SUM(a.total_pages) * 8, '#,0') AS TotalSpaceKB,