Many times when we  manage SQL server databases we need to  determine how much space each table is consuming on disk. In this blog we will learn two approaches for solving this frequently encountered common problem.

Approach 1:

We can use the below query to get table wise usage details:

SELECT 
    tab.NAME AS [Table Name],
    sch.Name AS [Schema Name],
    prt.rows AS [Row Counts],
    SUM(aunit.total_pages) * 8 AS [Total Space in KB], 
    SUM(aunit.used_pages) * 8 AS [Used Space in KB], 
    (SUM(aunit.total_pages) - SUM(aunit.used_pages)) * 8 AS [Unused Space in KB]
FROM 
    sys.tables tab
INNER JOIN      
    sys.indexes indx ON tab.OBJECT_ID = indx.object_id
INNER JOIN 
    sys.partitions prt ON indx.object_id = prt.OBJECT_ID AND indx.index_id = prt.index_id
INNER JOIN 
    sys.allocation_units aunit ON prt.partition_id = aunit.container_id
LEFT OUTER JOIN 
    sys.schemas sch ON tab.schema_id = sch.schema_id
WHERE 
    tab.is_ms_shipped = 0
	AND 
	indx.OBJECT_ID > 255 
GROUP BY 
    tab.Name, sch.Name, prt.Rows
ORDER BY 
    tab.Name

Approach 2:

If you are using SQL Server Management Studio, you can avoid  running the above  query and instead use a report.

To access the report follow the below steps:

1. Right click on the database
2. Navigate to Reports > Standard Reports > Disk Usage By Table