SQL script to view sizes of all tables


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.totalpages) - SUM(a.usedpages)) * 8 AS UnusedSpaceKB
FROM
    sys.tables t
INNER JOIN     
    sys.indexes i ON t.OBJECTID = i.objectid
INNER JOIN
    sys.partitions p ON i.objectid = p.OBJECTID AND i.indexid = p.indexid
INNER JOIN
    sys.allocationunits a ON p.partitionid = a.container_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.ismsshipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, p.Rows
ORDER BY
    t.Name

Last edited Jul 14, 2013 at 6:01 AM by sachinkaura, version 1

Comments

No comments yet.