Sometimes the name of a database and the file name won’t match, which makes it a bit of a pain to connect the two. Here is a script to search for databases that contains a specified phrase. The search looks at the entire path, not just the file name.

declare @FileName nvarchar(128) = ‘FileName’

select d.name as DatabaseName,
m.name as LogicalName, m.physical_name as FilePath, m.state_desc,
m.file_id, m.database_id, m.type_desc
from sys.master_files as m
join sys.databases as d
on d.database_id = m.database_id
where m.physical_name like ‘%’ + @FileName + ‘%’

go