MS SQL: List All Databases and File Sizes

-- Create a temp table to store results
CREATE TABLE #DatabaseFileSizes (
    DatabaseName NVARCHAR(128),
    LogicalFileName NVARCHAR(128),
    FileType NVARCHAR(60),
    PhysicalFilePath NVARCHAR(260),
    TotalSizeMB DECIMAL(18,2),
    UsedSpaceMB DECIMAL(18,2),
    FreeSpaceMB DECIMAL(18,2)
);

-- Cursor to loop through databases
DECLARE @DatabaseName NVARCHAR(128);
DECLARE @SQL NVARCHAR(MAX);

DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases
WHERE state_desc = 'ONLINE' AND name NOT IN ('master', 'tempdb', 'model', 'msdb');

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DatabaseName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = '
    USE [' + @DatabaseName + '];
    INSERT INTO #DatabaseFileSizes
    SELECT
        DB_NAME() AS DatabaseName,
        mf.name AS LogicalFileName,
        mf.type_desc AS FileType,
        mf.physical_name AS PhysicalFilePath,
        CAST(mf.size * 8.0 / 1024 AS DECIMAL(18,2)) AS TotalSizeMB,
        CAST(FILEPROPERTY(mf.name, ''SpaceUsed'') * 8.0 / 1024 AS DECIMAL(18,2)) AS UsedSpaceMB,
        CAST((mf.size - FILEPROPERTY(mf.name, ''SpaceUsed'')) * 8.0 / 1024 AS DECIMAL(18,2)) AS FreeSpaceMB
    FROM sys.database_files mf;
    ';
    EXEC sp_executesql @SQL;
    FETCH NEXT FROM db_cursor INTO @DatabaseName;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;

-- Final result
SELECT * FROM #DatabaseFileSizes ORDER BY DatabaseName, FileType;

-- Clean up
DROP TABLE #DatabaseFileSizes;