-- declare table variable to hold list of tables & indexed views
DECLARE @tables TABLE (  
  SchemaName VARCHAR(128)
, TableName VARCHAR(128)
, ObjectId INT
, Reported BIT DEFAULT 0
)

-- collect list of tables & indexed views
INSERT INTO @tables (
  SchemaName
, TableName 
, ObjectId
)
SELECT DISTINCT
  s.name
, o.name
, o.object_id
FROM sys.schemas s
  INNER JOIN sys.objects o ON o.schema_id = s.schema_id
  LEFT JOIN sys.indexes i ON i.object_id = o.object_id
WHERE o.type = 'U'
OR (o.type = 'V' AND i.index_id IS NOT NULL)

-- declare table variable to hold sp_spaceused results
DECLARE @objId INT
DECLARE @sql VARCHAR(MAX)

DECLARE @results TABLE (
  SchemaName VARCHAR(128)
, TableName VARCHAR(128)  
, TableRows CHAR(11)
, DataSize VARCHAR(18)  
, IndexSize VARCHAR(18)  
, ReservedSpace VARCHAR(18)  
, UnusedSpace VARCHAR(18)  
) 

-- call sp_spaceused for each table in @tables and cache results
WHILE 1 = 1
BEGIN 

  SET @objId = NULL

  SELECT TOP 1
    @objId = ObjectId
  FROM @tables
  WHERE Reported = 0

  IF @objId IS NULL
    BREAK

  SELECT 
    @sql = 'sp_spaceused @objname = ''[' + SchemaName + '].[' + TableName + ']'''
  FROM @tables
  WHERE ObjectId = @objId

  INSERT INTO @results (
    TableName
  , TableRows
  , ReservedSpace
  , DataSize
  , IndexSize
  , UnusedSpace
  )
  EXEC (@sql)

  UPDATE r
  SET r.SchemaName = t.SchemaName
  FROM @results r
    INNER JOIN @tables t ON t.ObjectId = @objId
  WHERE r.SchemaName IS NULL

  UPDATE @tables
  SET Reported = 1
  WHERE ObjectId = @objId

END

-- inspect cached results
SELECT 
  SchemaName
, TableName
, CAST(TableRows AS INT) AS TableRows
, ROUND(CAST(REPLACE(DataSize, 'KB', '') AS FLOAT)/1024, 1) AS DataMb
, ROUND(CAST(REPLACE(IndexSize, 'KB', '') AS FLOAT)/1024, 1) AS IndexMb
, ROUND(CAST(REPLACE(UnusedSpace, 'KB', '') AS FLOAT)/1024, 1) AS UnusedMb
, ROUND(CAST(REPLACE(ReservedSpace, 'KB', '') AS FLOAT)/1024, 1) AS ReservedMb
FROM @results
ORDER BY 4 DESC