Find the database a particular MDF file belongs to

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Find the database a particular MDF file belongs to

Author

Topic

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-14 : 05:44:46

Hi All,

I'm trying to clear down our development db server (where engineers have been creating databases for test purposes and then forgetting all about them). So there are a *lot* of databases sitting on this server.

I've found a few Mdf / ldf pairs with no meaningful name and would like to find out which database they belong to.

How does one do this?

-------------
Charlie

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-14 : 08:33:36

Try

select name from master..sysdatabases
where filename like '%your_mdf_file%'

Madhivanan

Failing to plan is Planning to fail

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-14 : 08:35:41

select [name] as [Database name],physical_name from sys.master_files

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-14 : 08:44:29

Perfect, both of you. Thanks,

I'd got as far as sys.databases but it didn't contain the info I was looking for.

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-14 : 08:57:49

In fact, to get the logical database name I had to link to sys.databases anyway.


SELECT
	sd.[name] AS [Database Name]
	, smf.[physical_name] AS [MDF Filename]
FROM
	sys.master_files smf
	JOIN sys.databases sd ON sd.[database_Id] = smf.[database_Id]
WHERE
	smf.[physical_name] LIKE '%FGW%'

This is because the logical database name in sys.master_files was the same for all the databases on my development server (just because they are all different datasets / versions for the same database schema).

Thanks once again. I have tracked down the culprit.

-------------
Charlie

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 09:05:13

[code]DECLARE @Files TABLE
(
Name SYSNAME,
FileName VARCHAR(256),
Size BIGINT
)

INSERT @Files
EXEC sp_msforeachdb 'SELECT name, filename, size FROM ?.sys.sysfiles'

SELECT *
FROM @Files[/code]

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-14 : 09:06:06

You can also use

EXEC sp_helpdb 'your database name'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources