TSQL – Get all table sizes


Introduction

This example i found quite useful. The current Syteline database I am administering peaked at 176Gb in size, and although we followed Infor’s sizing advise when acquiring the hardware (infact we doubled most of their suggestions), we were running out of space – especially if you wanted to restore a backup for one reason or another! What I needed was a way to work out what size each table was then work out if I can reduce the size in any way, as a new set of disks will set up back £1200+ !

The following script was robbed straight off of the internet – I am reposting it here as you may well find it useful too. To view the original article see http://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database

The Code

SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
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
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
AND isnull(i.[name], ‘Heap’) = ‘Heap’
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
t.Name

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.