Search

Monday, December 29, 2014

Tables ordered by Size

Use below query to find the tables in a database based on Size

SELECT O.Name AS [Table], 
       SUM(A.Total_Pages) AS [Reserved Pages],
       SUM(A.Used_Pages) AS [Used Pages],
       (SUM(A.Total_Pages) * 8 / 1024) AS [Reserved (MB)],
       (SUM(A.Used_Pages) * 8 / 1024) AS [Used (MB)],
       SUM(CASE WHEN A.Type <> 1 THEN A.Used_Pages
                WHEN P.Index_ID < 2 THEN A.Data_Pages
                ELSE 0
                END) AS Pages,
       SUM(CASE WHEN ( P.Index_ID < 2 )
                AND ( A.Type = 1 ) THEN P.Rows
                ELSE 0
                END) AS [Rows]
FROM Sys.Objects AS O
       JOIN Sys.Partitions AS P ON P.Object_ID = O.Object_ID
       JOIN Sys.Allocation_Units A ON P.Partition_ID = A.Container_ID
WHERE O.Type = 'U'
GROUP BY O.Name
ORDER BY [Used Pages] DESC

No comments:

Post a Comment