Any way to quickly tell which database, if any, is attached to a .mdf file?

Question

Assume SQL Server 2005 / 2008 with a large number of databases. Is there any way to quickly tell which database, if any, is attached to a particular .mdf file?

We've dropped some databases over time and would like to clean up some lingering .mdf's to clear up space on the server. Currently the only way I know of is to look at the properties of each database, one by one, in Management Studio and make a list of the files they're attached to. Looking for something a little more efficient than this, if anything exists.

Answer 1

sys.master_files contains one row per database for the first file (id = 1) for that database. That is, system tables will always be in fileid = 1 for each database

This is all you need:

SELECT
   DB_NAME(database_id), physical_name
FROM
   sys.master_files

Answer 2

This may help.

declare @files table (
    db_name sysname,
    physical_name nvarchar(260)
)

insert into @files
    exec sp_MSforeachdb 'select "?", physical_name from ?.sys.database_files'

select db_name, physical_name 
    from @files