RobSymonds.com

DECLARE
 @SPID INT,
 @Handle binary(20),
 @retVal varchar(8000)

SET
 @SPID = 148

SELECT
 @Handle=sql_handle
FROM
 master..sysprocesses
WHERE spid = @SPID

SELECT
 SqlCommand=convert(varchar(8000), [text])
FROM
 ::fn_get_sql(@Handle)

USE [master]

SELECT
CAST(SERVERPROPERTY(’productversion’) AS VARCHAR) + ‘ - ‘
+ CAST(SERVERPROPERTY(’productlevel’) AS VARCHAR) + ‘ (’
+ CAST(SERVERPROPERTY(’edition’) AS VARCHAR) + ‘)’

DECLARE @dbs TABLE (dbname NVARCHAR(200))
DECLARE @wrkStatement NVARCHAR(200)
DECLARE @wrkSql NVARCHAR(4000)

INSERT INTO @dbs(dbname)
    SELECT name
    FROM master..sysdatabases
    WHERE name NOT IN (’model’, ‘master’, ‘msdb’)
    ORDER BY name

CREATE TABLE #tmp123_results (
    [Db] NVARCHAR(100), 
    [FileId] INT,
    [LogicalName] NVARCHAR(655),
    [AllocatedMB] DECIMAL(12, 2),
    [UsedMB] DECIMAL(12, 2),
    [FreeMB] DECIMAL(12, 2),
    [PercentUsed] DECIMAL(12, 2),
    [PercentFree] DECIMAL(12, 2),
    [PhysicalName] NVARCHAR(655)
)

DECLARE @curr_db NVARCHAR(100)
DECLARE @crsSelect CURSOR

SET @crsSelect = CURSOR FOR
    SELECT dbname
    FROM @dbs

OPEN @crsSelect

FETCH NEXT FROM @crsSelect INTO @curr_db
WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @wrkStatement = ‘['+@curr_db+ '].dbo.sp_executesql’
        SET @wrkSql = ‘INSERT INTO #tmp123_results(Db,FileId,LogicalName,AllocatedMB,UsedMB,FreeMB,PercentUsed,PercentFree,PhysicalName)
            SELECT
            DB = ”’+@curr_db+”’,
            a.FILEID,
            NAME =  a.NAME,
            ALLOCATED_MB = convert(decimal(12,2),round(a.size/128.000,2)),
            USED_MB = convert(decimal(12,2),round(fileproperty(a.name,”SpaceUsed”)/128.000,2)),
            FREE_MB = convert(decimal(12,2),round((a.size-fileproperty(a.name,”SpaceUsed”))/128.000,2)) ,
            PercentUsed = 100 * (convert(decimal(12,2),round((fileproperty(a.name,”SpaceUsed”))/128.000,2)) / convert(decimal(12,2),round(a.size/128.000,2)) ) ,
            PercentFree = 100 * (convert(decimal(12,2),round((a.size-fileproperty(a.name,”SpaceUsed”))/128.000,2)) / convert(decimal(12,2),round(a.size/128.000,2)) ) ,
            FILENAME = a.FILENAME
            FROM [' + @curr_db + '].dbo.sysfiles a’ 

        EXEC @wrkStatement @wrkSql

        FETCH NEXT FROM @crsSelect INTO @curr_db
    END

CLOSE @crsSelect
DEALLOCATE @crsSelect

SELECT * FROM #tmp123_results ORDER BY Db, FileId

DROP TABLE #tmp123_results