Script to find out the space reserved and used by sqlserver on a per table basis
DECLARE @ShowResultsInKB1_MB0 BIT
SET @ShowResultsInKB1_MB0 = 0;
WITH TableSizes AS (
SELECT
a3.name AS [schemaname],
a2.name AS [tablename],
a1.ROWS AS ROW_COUNT,
(a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,
a1.DATA * 8 AS DATA,
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.DATA THEN (a1.used + ISNULL(a4.used,0)) - a1.DATA ELSE 0 END) * 8 AS index_size,
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused
FROM
(SELECT
ps.object_id,
SUM (
CASE
WHEN (ps.index_id < 2) THEN ROW_COUNT
ELSE 0
END
) AS [ROWS],
SUM (ps.reserved_page_count) AS reserved,
SUM (
CASE
WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
) AS DATA,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
(SELECT
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.TYPE <> N'S' AND a2.TYPE <> N'IT'
)
SELECT SchemaName, TableName, ROW_COUNT
, CAST(ROUND(CASE @ShowResultsInKB1_MB0 WHEN 1 THEN reserved ELSE reserved / 1024.0 END, 2) AS DECIMAL(18,2)) AS reserved
, CAST(ROUND(CASE @ShowResultsInKB1_MB0 WHEN 1 THEN DATA ELSE DATA / 1024.0 END, 2) AS DECIMAL(18,2)) AS DATA
, CAST(ROUND(CASE @ShowResultsInKB1_MB0 WHEN 1 THEN index_size ELSE index_size / 1024.0 END, 2) AS DECIMAL(18,2)) AS index_size
, CAST(ROUND(CASE @ShowResultsInKB1_MB0 WHEN 1 THEN unused ELSE unused / 1024.0 END, 2) AS DECIMAL(18,2)) AS unused
FROM TableSizes
ORDER BY ROW_COUNT DESC,SchemaName, TableName
example output