SQL: How to determine free space and file size for SQL Server databases

DECLARE @FileSize AS TABLE (
	dbName NVARCHAR(128)
	, FileName NVARCHAR(128)
	, type_desc NVARCHAR(128)
	, CurrentSizeMB DECIMAL(10, 2)
	, FreeSpaceMB DECIMAL(10, 2)
	);

INSERT INTO @FileSize (
	dbName
	, FileName
	, type_desc
	, CurrentSizeMB
	, FreeSpaceMB
	)
EXEC sp_msforeachdb 
	'use [?]; 
 SELECT DB_NAME() AS DbName, 
        name AS FileName, 
        type_desc,
        size/128.0                                                           AS CurrentSizeMB,  
        size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0    AS FreeSpaceMB
FROM sys.database_files
WHERE type IN (0,1);'
	;

SELECT dbName
	, FileName
	, type_desc
	, FORMAT(X.CurrentSizeMB, 'N0') AS CurrentSizeMB
	, FORMAT(X.FreeSpaceMB, 'N0') AS FreeSpaceMB
FROM @FileSize X
WHERE dbName NOT IN ('distribution', 'master', 'model', 'msdb')
	AND FreeSpaceMB > 1000
ORDER BY FreeSpaceMB DESC

Found here.