Loading ...

SQL: Find size of all tables in a database | CodeAsp.Net

SQL: Find size of all tables in a database

 /5
0 (0votes)

In this blog I will show the script to get the size of all tables in a database. Records will be sorted by size descending. I am using [AdventureWorks] DB to demonstrate the example below:

;
WITH    CTE
          AS ( SELECT   t.NAME AS TableName,
                        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
               WHERE    t.NAME NOT LIKE 'dt%'
                        AND t.is_ms_shipped = 0
                        AND i.OBJECT_ID > 255
               GROUP BY t.Name,
                        p.Rows
             )
    SELECT  *
    FROM    CTE
    ORDER BY TotalSpaceKB DESC


Following is the output I got at my screen:


Comments (no comments yet)

Top Posts