DECLARE @DBInfo TABLE  
(
    ServerName SYSNAME,
    DatabaseName SYSNAME,
    LogicalFileName SYSNAME,
    PhysicalFileName NVARCHAR(520),
    /* [Size], [SpaceUsed] are identified as number of 8 KB pages */
    Size INT,
    SpaceUsed INT,
    SpaceFree AS (Size - SpaceUsed),
    SizeMB AS (Size / 128.0),
    SpaceUsedMB AS (SpaceUsed / 128.0),
    SpaceFreeMB AS ((Size - SpaceUsed) / 128.0),
    SpaceUsedPct AS (100 - (((Size / 128.0) - (SpaceUsed / 128.0)) / (Size / 128.0))),
    SpaceFreePct AS (((Size / 128.0) - (SpaceUsed / 128.0)) / (Size / 128.0)),
    Status SYSNAME,
    Updateability SYSNAME,
    RecoveryModel SYSNAME,
    PollDate DATETIME
);

DECLARE @PollDate NVARCHAR(23);
DECLARE @command VARCHAR(5000);

SET @PollDate = CONVERT(NVARCHAR(23), GETDATE(), 121);

SELECT @command = 
    '
    USE ;
    SELECT @@SERVERNAME AS ServerName,
        ''?'' AS DatabaseName,  
        sysfiles.name AS LogicalFileName,
        sysfiles.filename AS PhysicalFileName,
        sysfiles.size,
        FILEPROPERTY(sysfiles.name, ' + '''' + 'SpaceUsed' + '''' + ' ),
        CONVERT(sysname,DatabasePropertyEx(''?'', ''Status'')) AS Status,  
        CONVERT(sysname,DatabasePropertyEx(''?'', ''Updateability'')) AS Updateability,  
        CONVERT(sysname,DatabasePropertyEx(''?'', ''Recovery'')) AS RecoveryMode,
        ''' + @PollDate + '''
    FROM dbo.sysfiles
    ';

INSERT INTO @DBInfo  
(
    ServerName,  
    DatabaseName,  
    LogicalFileName,  
    PhysicalFileName,  
    Size,
    SpaceUsed,
    Status,  
    Updateability,  
    RecoveryModel,  
    PollDate
)
EXEC sp_MSForEachDB @command

SELECT
    *
FROM @DBInfo AS DI
ORDER BY ServerName, DatabaseName, DI.LogicalFileName

quote:

---

Originally posted by obsoletedude

thank you tfountain. That is exactly what I needed.

---

quote:

---

Originally posted by tkizer

Run FILEPROPERTY using sp_executesql that way it runs in the context of the correct database.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

---