List of all Data and Log Files of a SQL Instance

In order to list the files, we just need to query the system table SysaltFiles.

SELECT * FROM SysAltFiles
The below image shows the result

This  query can be further tweaked to get only the data files or only the log files by adding where clause and filter by groupid column. Groupid = 1 indicates it is a data file and groupid = 0 indicates it is a log file.