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