• Get the size of all tables in database

    Posted on June 24, 2013 by in SQL Tips & Tricks, Tips & Tricks

    SELECT 
        systables.name AS TableName,
        syspartitions.rows AS [RowCount],
        SUM(units.total_pages) * 8 AS TotalSpace_KB, 
        (SUM(units.total_pages) * 8) / 1024 AS TotalSpace_MB, 
        SUM(units.used_pages) * 8 AS UsedSpace_KB,
        (SUM(units.used_pages) * 8) / 1024 AS UsedSpace_MB
    FROM 
        sys.tables systables
    INNER JOIN sys.indexes sysindices 
    	ON systables.OBJECT_ID = sysindices.object_id
    INNER JOIN sys.partitions syspartitions 
    	ON sysindices.object_id = syspartitions.OBJECT_ID AND sysindices.index_id = syspartitions.index_id
    INNER JOIN sys.allocation_units units 
    	ON syspartitions.partition_id = units.container_id
    GROUP BY 
        systables.Name, syspartitions.Rows
    ORDER BY 
        TotalSpace_KB desc

    size of all tables

    Be Sociable, Share!
      Post Tagged with , ,

    Written by

    View all articles by

    Email : [email protected]

    Leave a Reply