Script To Find Space Used By All Tables In Sql Server





Couple of times i face issues require size of all tables in my database. This script is share by team members to get quick result.

Below script will return list of all tables with there size in GB and MB (total , used , unused)
Output Will be as below.




/************************************************************************************/
SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    (SUM(a.total_pages) * 8)/(1024) AS TotalSpaceMB, 
(SUM(a.total_pages) * 8)/(1024*1024) AS TotalSpaceGB, 
    (SUM(a.used_pages) * 8)/(1024) AS UsedSpaceMB, 
(SUM(a.used_pages) * 8)/(1024*1024) AS UsedSpaceGB,
   ((SUM(a.total_pages) - SUM(a.used_pages)) * 8) /(1024) AS UnusedSpaceMB,
((SUM(a.total_pages) - SUM(a.used_pages)) * 8) /(1024*1024) AS UnusedSpaceGB
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 
    TotalSpaceMB desc

/************************************************************************************/

No comments:

Post a Comment