CREATE TABLE #temp

       (

       [name] nvarchar(128),

       [rows] char(11),

       [reserved] varchar(18),

       [data] varchar(18),

       [index_size] varchar(18),

       [unused] varchar(18)

       )

 

INSERT INTO #temp

EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?' "

 

SELECT

       [name] AS TableName,

       CAST([rows] AS int) AS NumOfRows,

       CAST(REPLACE([reserved],' KB','') AS int) AS [TotalKB]

FROM

       #temp

ORDER BY 3 DESC

 

SELECT SUM(CAST(REPLACE([reserved],' KB','') AS int)) AS TotalKB_data FROM #temp

 

DROP TABLE #temp