Sometimes it might be difficult for find size of all tables since it involves calling sp_spaceused stored procedure against each table. This is still worse when the number of tables are huge. The attached script lists all tables with number of rows, reserved, actual data size, index size and unused space in a database.
Find size of all tables in a database
Declare @v_TableName Varchar(100) Declare @v_Table Table (Table_Name Varchar(100)) Declare @v_TableSize Table ( Table_Name Varchar(100), rows BigInt, Reserved Varchar(50), ActualDataSize Varchar(50), IndexSize Varchar(50), Unused Varchar(50) ) Insert Into @v_Table Select Table_Name From Information_Schema.Tables Where Table_Type = 'BASE TABLE' Select @v_TableName = Table_Name From @v_Table While Exists (Select 1 From @v_Table) Begin Insert Into @v_TableSize exec sp_spaceused @v_TableName Delete From @v_Table Where Table_Name = @v_TableName Select @v_TableName= Table_Name From @v_Table End Select * From @v_TableSize Order By rows Desc Go