How can I check the disk space usage of a SQL Server table using T-SQL?

Data and Databases
1

Hey everyone,

I’m trying to figure out how to check the disk space a table is using in SQL Server, but I want to do it with T-SQL instead of the GUI. I know you can right-click on a table and go to properties to see this info, but that’s not what I’m after.

Does anyone know a T-SQL query or command that can show me how much space a specific table is taking up on the disk? It would be super helpful for managing our database storage.

I’ve tried looking online, but most solutions involve using the GUI. Any T-SQL wizards out there who can point me in the right direction? Thanks in advance for any help!

2

hey lucaspixel23, u can use sp_spaceused stored procedure. just do this:

EXEC sp_spaceused ‘YourTableName’;

it’ll show u stuff like row count, reserved space, data space etc. quick n easy way to check table size without messin with the gui. hope that helps!

3

hm, interesting question! have u tried using sys.dm_db_partition_stats? it’s pretty nifty for getting table sizes. something like:

SELECT SUM(used_page_count) * 8 / 1024.0 AS used_space_mb
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID(‘YourTableName’)

what do u think? it might give u a different perspective on space usage :thinking:

4

To check disk space usage for a specific table using T-SQL, you can utilize the sp_spaceused system stored procedure. Here’s a simple query:

EXEC sp_spaceused 'YourTableName';

This will return information about the table’s row count, reserved space, data space, index size, and unused space. For more detailed information, including individual data and index sizes, you can use:

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB
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
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME = 'YourTableName'
GROUP BY 
    t.Name, s.Name, p.Rows;

This query provides a more comprehensive view of the table’s space usage.