Listing information about all database files in SQL Server

You can use sys.master_files.

Contains a row per file of a database as stored in the master
database. This is a single, system-wide view.