How to calculate size of all tables in ms sql ?

DECLARE @table table(Id int IDENTITY(1,1)
, Name varchar(256))

INSERT INTO @table
SELECT b.name + ‘.’+ a.name
FROM sys.tables a INNER JOIN sys.schemas b
ON a.schema_id = b.schema_id

INSERT INTO @table
SELECT ‘-1’

DECLARE @result table( TableName varchar(256)
, TotalRows int
, Reserved varchar(50)
, DataSize varchar(50)
, IndexSize varchar(50)
, UnusedSize varchar(50))

DECLARE @temp varchar(256)
DECLARE @index int
SET @index = 1

WHILE 1=1
BEGIN
SELECT @temp = Name
FROM @table
WHERE Id = @index

IF @temp = ‘-1′
BREAK

INSERT @result( TableName
, TotalRows
, Reserved
, DataSize
, IndexSize
, UnusedSize)
EXEC sp_spaceused @temp

SET @index = @index + 1
END

SELECT c.name+’.’+b.name as [table]
, a.*
FROM @result a
INNER JOIN sys.tables b
ON a.TableName = b.name
INNER JOIN sys.schemas c
ON b.schema_id = c.schema_id
ORDER BY TotalRows DESC

source: http://www.codemeit.com/sql/mssql-list-all-tables-with-size-and-row-count.html