SELECT
    D.name,
    F.Name AS FileType,
    F.physical_name AS PhysicalFile,
    F.state_desc AS OnlineStatus,
    (F.size*8)/1024  AS FileSizeMB,
	(F.size*8) AS SizeInBytes
FROM 
    sys.master_files F
    INNER JOIN sys.databases D ON D.database_id = F.database_id
WHERE
	D.name != 'tempdb'
ORDER BY
    FileSizeMB desc --D.name