This might be a good start.

use MyDB

go

DECLARE @names TABLE

(

id INT IDENTITY(1,1),

name NVARCHAR(100) NULL

)

DECLARE @ROWCOUNT INT

DECLARE @i INT = 1

DECLARE @STR nvarchar(100)

INSERT INTO @names(name) SELECT name FROM sys.Tables

SET @ROWCOUNT = @@ROWCOUNT

DECLARE @space TABLE

(

name NVARCHAR(100) NULL,

rows CHAR(11),

reserved NVARCHAR (15),

data NVARCHAR (18),

indexes NVARCHAR (18),

unused NVARCHAR (18)

)

WHILE @i <= @ROWCOUNT

BEGIN

SELECT @STR = name FROM @names WHERE id = @i

INSERT INTO @space

EXEC sp_spaceused @STR

SET @i += 1

END

SELECT * FROM @space

ORDER BY CONVERT( BIGINT, rows ) DESC

;