i built this proc from some examples i found here on SQLServerCentral:

IF OBJECT_ID('[dbo].[sp_dbspaceused]') IS NOT NULL

DROP PROCEDURE [dbo].[sp_dbspaceused]

GO

--#################################################################################################

--developer utility function added by Lowell, used in SQL Server Management Studio

--Purpose: Get the file size and available space for every database file

--#################################################################################################

CREATE PROCEDURE sp_dbspaceused

AS

BEGIN

SET NOCOUNT ON

DECLARE @CurrentDB NVARCHAR(128)

-- The table #tblServerDatabases holds the names of databases on the server.

-- This table is used here to allow us to loop through each database,

-- rather than to run undocumented procedures, such as sp_msforeachdb

-- (which eliminated the need for a loop).

IF OBJECT_ID('tempdb..#tblServerDatabases', 'U') IS NOT NULL

DROP TABLE #tblServerDatabases

CREATE TABLE #tblServerDatabases (DBName NVARCHAR(128))

-- The table #tblDBFilesExtendedInfo holds the data and log files info

-- (name, size, used, free spaces, etc.).

IF OBJECT_ID('tempdb..#tblDBFilesExtendedInfo', 'U') IS NOT NULL

DROP TABLE #tblDBFilesExtendedInfo

CREATE TABLE #tblDBFilesExtendedInfo (

Idx INT IDENTITY(1, 1),

FileID INT,

FileGroupID INT,

TotalExtents BIGINT,

UsedExtents BIGINT,

DBFileName NVARCHAR(128),

LogicalFileName NVARCHAR(128),

DBFilePath NVARCHAR(1024),

DBFileType VARCHAR(16),

DBName NVARCHAR(128),

[TotalFileSize(MB)] MONEY,

[TotalUsed(MB)] MONEY,

[TotalFree(MB)] MONEY,

[SpaceUsed(%)] MONEY,

Status INT)

-- This table will hold the output of sp_helpfile, for each database.

-- This is needed in order to get the log file path and file name.

IF OBJECT_ID('tempdb..#tblDBFilesBasicInfo', 'U') IS NOT NULL

DROP TABLE #tblDBFilesBasicInfo

CREATE TABLE #tblDBFilesBasicInfo (

DBName NVARCHAR(128),

DBFileName NVARCHAR(128),

FileID INT,

FilePath NVARCHAR(1024),

FileGroupDesc NVARCHAR(128),

FileSizeKB NVARCHAR(64),

MaxSizeDesc NVARCHAR(64),

Growth NVARCHAR(64),

Usage NVARCHAR(64))

-- First - the data files are handled throught the

-- DBCC SHOWFILESTATS command.

INSERT INTO #tblServerDatabases (DBName)

SELECT [name]

FROM master.dbo.sysdatabases

SELECT @CurrentDB = MIN(DBName)

FROM #tblServerDatabases

WHILE @CurrentDB IS NOT NULL

BEGIN

INSERT INTO #tblDBFilesExtendedInfo(

FileID,

FileGroupID,

TotalExtents,

UsedExtents,

DBFileName,

DBFilePath)

EXEC ('USE [' + @CurrentDB + '] DBCC SHOWFILESTATS')

UPDATE #tblDBFilesExtendedInfo

SET DBName = @CurrentDB,

DBFileType = 'Data File'

WHERE DBName IS NULL

-- Run the sp_helpfile in order to get log file data.

INSERT INTO #tblDBFilesBasicInfo(

DBFileName,

FileID,

FilePath,

FileGroupDesc,

FileSizeKB,

MaxSizeDesc,

Growth,

Usage)

EXEC ('USE [' + @CurrentDB + '] EXEC sp_helpfile ')

UPDATE #tblDBFilesBasicInfo

SET DBName = @CurrentDB

WHERE DBName IS NULL

SELECT @CurrentDB = MIN(DBName)

FROM #tblServerDatabases WITH (NOLOCK)

WHERE DBName > @CurrentDB

END

-- Update the total file size, used and free space, based on the

-- extents information returned from DBCC SHOWFILESTATS.

UPDATE #tblDBFilesExtendedInfo

SET [DBFileName] = RIGHT(DBFilePath, CHARINDEX('\', REVERSE(DBFilePath)) -1),

[TotalFileSize(MB)] = CAST(((TotalExtents*64) / 1024.00) AS MONEY),

[TotalUsed(MB)] = CAST(((UsedExtents*64) / 1024.00) AS MONEY),

[TotalFree(MB)] = CAST(((TotalExtents*64) / 1024.00) AS MONEY)

- CAST(((UsedExtents*64) / 1024.00) AS MONEY),

[SpaceUsed(%)] = CASE

WHEN CAST(((TotalExtents*64) / 1024.00) AS MONEY) = 0.0 THEN 0.0

ELSE (CAST(((UsedExtents*64) / 1024.00) AS MONEY)*100)

/ CAST(((TotalExtents*64) / 1024.00) AS MONEY)

END

-- We are now done with the data file statuses, and we shall move

-- on to get the log files info, by using DBCC SQLPERF(LOGSPACE)

INSERT INTO #tblDBFilesExtendedInfo (DBName, [TotalFileSize(MB)], [SpaceUsed(%)], Status)

EXEC('DBCC SQLPERF(LOGSPACE)')

UPDATE a

SET [TotalUsed(MB)] = (a.[SpaceUsed(%)]/100.00)*a.[TotalFileSize(MB)],

[TotalFree(MB)] = (1.0 - (a.[SpaceUsed(%)]/100.00))*a.[TotalFileSize(MB)],

DBFileType = 'Log file',

DBFilePath = b.FilePath,

DBFileName = RIGHT(b.FilePath, CHARINDEX('\', REVERSE(b.FilePath)) -1)

FROM #tblDBFilesExtendedInfo a

INNER JOIN #tblDBFilesBasicInfo b

ON a.DBName = b.DBName

WHERE a.DBFileType IS NULL

AND b.Usage = 'log only'

--we want the logical file name as well, in case we need it for other purposes, like getting everything in a single spot to move files

UPDATE MyTarget

SET MyTarget.LogicalFileName = mff.name

FROM #tblDBFilesExtendedInfo MyTarget

INNER JOIN sys.master_files mff

ON MyTarget.DBFilePath = mff.physical_name

SET NOCOUNT OFF

-- That's it. We now need to return a readable recordset.

SELECT DBName,

DBFileType,

LogicalFileName,

DBFileName,

DBFilePath,

[TotalFileSize(MB)],

[TotalUsed(MB)],

[SpaceUsed(%)],

[TotalFree(MB)]

FROM #tblDBFilesExtendedInfo WITH (NOLOCK)

ORDER BY DBName ASC, DBFileType ASC, FileGroupID ASC, FileID ASC

-- Cleanup

IF OBJECT_ID('tempdb..#tblServerDatabases', 'U') IS NOT NULL

DROP TABLE #tblServerDatabases

IF OBJECT_ID('tempdb..#tblDBFilesExtendedInfo', 'U') IS NOT NULL

DROP TABLE #tblDBFilesExtendedInfo

IF OBJECT_ID('tempdb..#tblDBFilesBasicInfo', 'U') IS NOT NULL

DROP TABLE #tblDBFilesBasicInfo

END -- PROC