File Distribution Drive

 SELECT sf.[name], sf.filename, sd.state_desc, SUBSTRING (sf.filename, 1, 3) AS [drive],
CASE REVERSE(SUBSTRING (REVERSE(filename), 1, 3))
 WHEN 'mdf' THEN 'primary data'
 WHEN 'ndf' THEN 'secondary_data'
 WHEN 'ldf' THEN 'log_file'
 ELSE 'data_file'
END  AS file_type--,  *
FROM sys.sysaltfiles sf
INNER JOIN sys.databases sd
ON sf.dbid = sd.database_id
WHERE sd.state_desc = 'ONLINE'
AND sf.dbid > 4
ORDER BY SUBSTRING (sf.filename, 1, 3)