Home » Database » Find Size of table in SQL Server Database.

Find Size of table in SQL Server Database.

To find Size of each table of database in sql server.
Query use System table to get table name and allocation_units table to get total no of pages for each table.

SELECT<br />
    t.NAME AS TableName,<br />
    s.Name AS SchemaName,<br />
    p.rows AS RowCounts,<br />
    SUM(a.total_pages) * 8 AS TotalSpaceKB,<br />
    SUM(a.used_pages) * 8 AS UsedSpaceKB,<br />
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB<br />
FROM<br />
    sys.tables t<br />
INNER JOIN<br />
    sys.indexes i ON t.OBJECT_ID = i.object_id<br />
INNER JOIN<br />
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id<br />
INNER JOIN<br />
    sys.allocation_units a ON p.partition_id = a.container_id<br />
LEFT OUTER JOIN<br />
    sys.schemas s ON t.schema_id = s.schema_id<br />
WHERE<br />
    t.NAME NOT LIKE 'dt%'<br />
    AND t.is_ms_shipped = 0<br />
    AND i.OBJECT_ID &amp;gt; 255<br />
GROUP BY<br />
    t.Name, s.Name, p.Rows<br />
ORDER BY<br />
    t.Name<br />

It give result as below for sample database

Query result

Query result for an example database.

Leave a Reply

Subscribe

Join 901 other subscribers