How to match db name in EM with native file names

Question

Post reply

How to match db name in EM with native file names

Phil Parkin

SSC Guru

Points: 246963
More actions
March 14, 2004 at 8:34 pm

#161239

We have a SQL Server dev box that has loads of databases attached. In Explorer, I recently found an mdf file in an inappropriate folder. I could not move the file - it was locked by SQL Server - but the filename was obviously radically different from the database name, as it was not obvious which database the file corresponded with.
Is there a fast alternative to me having to wade through all the dbs in EM and checking properties so that I can correspond the filenames/locations with the database names?
Thanks.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

wz700 SSCertifiable Points: 7133 More actions · Answer 1

Execute from query analyzer

declare @sql varchar(400)

set @sql='select name, fileid, filename from ?.dbo.sysfiles'

exec sp_msforeachdb @sql

Phil Parkin SSC Guru Points: 246963 More actions · Answer 2

Thanks v much, that's nearly exactly what I need. But the 'Name' field that is returned appears to be the logical file name corresponding with the data/log file selected (and if someone could explain the significance of these, it would also be of interest). Now this might not be good practice, but we have several databases hanging around that have the same 'Filename' (eg Testdata_data, Testdata_log), but different locations (eg d:\sqldata\companya.mdf ...).

So ... it's still not always obvious what files correspond with what database. Is there any way of extracting the actual database name as well?

Thanks again, Phil

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

wz700 SSCertifiable Points: 7133 More actions · Answer 3

declare @sql varchar(400)

set @sql='select ''?''as dbname;select name, fileid, filename from ?.dbo.sysfiles'

exec sp_msforeachdb @sql

database name is also listed.

Phil Parkin SSC Guru Points: 246963 More actions · Answer 4

Ace - that will be filed in my 'Useful SQL Utilities' folder, thanks.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin