Sometimes you find yourself looking for which database use a specific data file on your filesystem. This may happen when restoring/renaming a SQL database on your server and not following your naming convention.
This usually ends up with different physical names that don’t match the database names, and on a larger SQL Server that soon becomes a problem.
This script helps you find which physical files a database have.
SELECT db.name as database_name,files.physical_name, files.name as logical_name FROM master.sys.master_files files inner join master.sys.databases db on files.database_id = db.database_id -- and you can narrow it down to just showing the -- file you are looking for: where files.physical_name like '%<part of the filename>%'