I just had to have fun with this.

I had to try like 15 different things to whack any sort of loop. Anyhow this is really nice and usable. Enjoy :

USE master

GO

SET STATISTICS IO, TIME OFF

SET NOCOUNT ON

SET IMPLICIT_TRANSACTIONS ON

GO

IF OBJECT_ID('dbo.spaceused', 'U') IS NULL

BEGIN

CREATE TABLE dbo.spaceused (

DbName sysname DEFAULT(''),

tblName sysname,

Row_count INT ,

Reserved VARCHAR(50),

data VARCHAR(50) ,

index_size VARCHAR(50),

unused VARCHAR(50),

PRIMARY KEY CLUSTERED (DbName, tblName)

);

END

ELSE

BEGIN

--DROP TABLE dbo.spaceused

TRUNCATE TABLE dbo.spaceused

END

COMMIT

GO

DECLARE @Cmd VARCHAR(8000)

SET @Cmd = 'USE [?];

IF ''?'' NOT IN (''tempdb''

--, ''master'', ''model'', ''msdb''

)

BEGIN

--PRINT ''?''

DECLARE @InnerCmd VARCHAR(8000)

SET @InnerCmd = ''

EXEC sp_spaceused '''''' + CHAR(63) + ''''''''

INSERT INTO master.dbo.spaceused(tblName, Row_count,Reserved,data,index_size,unused)

EXEC sp_MSforeachtable @InnerCmd

UPDATE master.dbo.spaceused SET DbName = ''?'' WHERE DbName = ''''

END

'

--PRINT @Cmd

--EXEC sp_MSforeachtable @Cmd

EXEC sp_MSforeachdb @Cmd

SELECT

DbName

, tblName

, Row_count

, CONVERT(BIGINT, REPLACE(Reserved, ' KB', '')) / 1024 AS MB_Reserved

, CONVERT(BIGINT, REPLACE(data, ' KB', '')) / 1024 AS MB_data

, CONVERT(BIGINT, REPLACE(index_size, ' KB', '')) / 1024 AS MB_index_size

, CONVERT(BIGINT, REPLACE(unused, ' KB', '')) / 1024 AS MB_unused

FROM

dbo.spaceused

WHERE Row_count > 0

ORDER BY

DbName

, MB_Reserved DESC

, Row_count DESC

COMMIT