How to check table size in SQL

Sometimes when you’re having an issue with database timing out or slowdown, it’s always worth to check and investigate the abnormal growth of the table size in your database. Here’s a simple script that may do just that.

USE YourDB
 
SELECT 
    t.NAME AS NameOfTable,
    s.Name AS NameOfSchema,
    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.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 NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name

 

Hope this helps someone who is currently investigating issues related to the Database.

 

This article is brought to you by: First Brand Media LLC

Leave a Reply

Your email address will not be published. Required fields are marked *