Hi All,
We when planned to delete some unused tables and also to know how much some of tables consuming the space, we needed to have a quick on table size.
Then team approached me to write a quick query to know the table name and its size.
The query to calculate size of table in given database is as follows:
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 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
GROUP BY t.NAME
,s.NAME
,p.Rows
ORDER BY t.NAME
Comments
Post a Comment