Find a database from the file name

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: