Execute the following script in Query Editor to fetch sp_spaceused system procedure for each table in AdventureWorks db:

USE AdventureWorks
GO

DECLARE @SchemaName VARCHAR(255), @TableName VARCHAR(255)
DECLARE @AllTables TABLE(
TableName VARCHAR (255) collate database_default,
SchemaName VARCHAR(127) collate database_default )

DECLARE @TablesStats TABLE (
ID int IDENTITY(1,1)
, SchemaName sysname null
, TableName sysname
, [RowCount] INT
, Reserved VARCHAR(32) collate database_default
, Data VARCHAR(32) collate database_default
, IndexSize VARCHAR(32) collate database_default
, Unused VARCHAR(32) collate database_default )

INSERT INTO @AllTables
SELECT t.name, s.name
FROM sys.tables t
JOIN sys.schemas s
ON t.schema_id = s.schema_id

DECLARE curTable CURSOR FOR
SELECT s.name, s.name + '.' + t.name
FROM sys.tables t
JOIN sys.schemas s
ON t.schema_id = s.schema_id

OPEN curTable;
FETCH NEXT FROM curTable INTO @SchemaName, @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TableName = REPLACE(@TableName, '[','');
SET @TableName = REPLACE(@TableName, ']','');

IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@TableName))
BEGIN
PRINT @SchemaName +', '+ @TableName
INSERT @TablesStats ( TableName, [RowCount],Reserved,
Data,IndexSize, Unused )
EXEC sp_spaceused @TableName, true ;
UPDATE @TablesStats SET SchemaName=@SchemaName WHERE ID = (SELECT max(ID) from @TablesStats)
END

FETCH NEXT FROM curTable INTO @SchemaName, @TableName;
END;
CLOSE curTable;
DEALLOCATE curTable;

SELECT SchemaName, TableName, [RowCount], Reserved,
Data, IndexSize, Unused
FROM @TablesStats
ORDER BY SchemaName,TableName;

GO