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!
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.