Here's the full and final T-SQL code...

--COLLECTING INFORMATION

CREATE TABLE #TempDBSize(

[name] [varchar](100) NOT NULL,

[database_id] [int] NOT NULL,

[Size] [decimal](10, 2) NOT NULL,

[UsedSpace] [decimal](10, 2) NOT NULL

)

EXECUTE master.sys.sp_MSforeachdb

'

USE [?];

INSERT INTO #TempDBSize

SELECT

SD.name,

MF.database_id,

SUM( CONVERT(decimal(10,2),(DF.size/128.0)) ) as Size,

SUM( CONVERT(decimal(10,2), (CAST(FILEPROPERTY(DF.name, "SpaceUsed") AS INT)/128.0 ) ) ) AS UsedSpace

FROM sys.master_files MF JOIN sys.databases SD

ON SD.database_id = MF.database_id

JOIN sys.database_files DF

ON DF.physical_name collate DATABASE_DEFAULT = MF.physical_name collate DATABASE_DEFAULT

WHERE MF.type = 0

GROUP BY SD.name, MF.database_id

'

--UPDATING RECORD FOR EXISTING DATABASE

IF EXISTS( SELECT database_id FROM #TempDBSize WHERE database_id NOT IN (SELECT DISTINCT DBID FROM dbo.DBInfo))

BEGIN

INSERT INTO dbo.DBInfo

(DBName, DBID )

(SELECT

tds.name, tds.database_id

FROM #TempDBSize tds

WHERE tds.database_ID NOT IN (SELECT DISTINCT DBID from DBInfo WHERE DBID = tds.database_ID))

INSERT INTO dbo.DBSize

(DBID, Size, UsedSpace, MetricDate)

(SELECT

#TempDBSize.database_ID,

#TempDBSize.Size,

#TempDBSize.UsedSpace,

GetDate() as MetricDate

FROM #TempDBSize

)

END

ELSE

BEGIN

INSERT INTO dbo.DBSize

(DBID, Size, UsedSpace, MetricDate)

(SELECT

#TempDBSize.database_ID,

#TempDBSize.Size,

#TempDBSize.UsedSpace,

GetDate() as MetricDate

FROM #TempDBSize

)

END

DROP TABLE #TempDBSize;

That runs at midnight on all my serves and saves it on a local database for later usage. I configured reporting services in order to use and display the results in a nicely manner.

I was having a hard time getting the used space, which I consider important as well, but found this post by Greg Robidoux[/url] which shows how to use FILEPROPERTY function to get the used space. That helped me a lot.

I know there are more elegant ways to do it 😉 ... but it works ... tested on MS-SQL2005 and 2008.

Cheers,