Our today's discussion is how we can determine each table size of any database in MS SQL Server. This is Required some time database Size is increasing and some tables which may required optimization. So below query help.


SELECT

tbl.Name AS NAME_OF_TABLE

,sch.Name AS Name_of_Schema_of_Database

,ptr.rows AS TOTAL_NO_OF_ROWS

,CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS DECIMAL(18,3)) AS ALLOCATED_SPACE_IN_MB

,CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS DECIMAL(18,3)) AS UNALLOCATEDSPACE_IN_MB

,CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS DECIMAL(18,3)) AS TOTAL_ALLOCATED_SPACE_IN_MB

,CAST(CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS DECIMAL(18,3))/1024 AS decimal(18,3)) AS TOTAL_ALLOCATED_SPACE_IN_GB

FROM sys.tables tbl

INNER JOIN sys.indexes idx ON tbl.OBJECT_ID = idx.object_id

INNER JOIN sys.partitions ptr ON idx.object_id = ptr.OBJECT_ID AND idx.index_id = ptr.index_id

INNER JOIN sys.allocation_units a ON ptr.partition_id = a.container_id

INNER JOIN sys.schemas sch ON tbl.schema_id = sch.schema_id

GROUP BY tbl.Name, sch.Name, ptr.Rows

ORDER BY TOTAL_ALLOCATED_SPACE_IN_MB DESC