Here’s how I query to get sizing info for each table in a database

BEGIN TRAN GETTABLESIZES

DECLARE @TempSIZINGTable  TABLE
( [Table_Name] varchar(50),
Row_Count int,
Table_Size varchar(50),
Data_Space_Used varchar(50),
Index_Space_Used varchar(50),
Unused_Space varchar(50)
)
INSERT INTO @TempSIZINGTable EXEC('sp_msforeachtable ''sp_spaceused "?"''')

/*
sp_spaceused adds a KB to the end of the sizing fields
we use substring and a Pattern Index search for a space,
so that 1024 KB turns into 1024 which we convert to an int
for sorting
*/

SELECT [TABLE_NAME],
[ROW_COUNT],
CAST(substring( [TABLE_SIZE],1,PATINDEX('%[ ]%', [TABLE_SIZE])-1) AS INT) AS [TABLE_SIZE in KB],
CAST(substring( [DATA_SPACE_USED],1,PATINDEX('%[ ]%', [DATA_SPACE_USED])-1) AS INT) AS [DATA_SPACE_USED in KB],
CAST(substring( [INDEX_SPACE_USED],1,PATINDEX('%[ ]%', [INDEX_SPACE_USED])-1) AS INT) AS [INDEX_SPACE_USED in KB],
CAST(substring( [UNUSED_SPACE],1,PATINDEX('%[ ]%', [UNUSED_SPACE])-1) AS INT) AS [UNUSED_SPACE in KB]
FROM @TempSIZINGTable
ORDER BY [TABLE_SIZE in KB] desc

COMMIT TRAN GETTABLESIZES