select mdf and ldf file locations from the master

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 2000 Forums

SQL Server Development (2000)

select mdf and ldf file locations from the master

Author

Topic

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2004-07-08 : 04:04:52

Hi there,

I use the following query to select the filename for each database in my sql server. Is is possible to get the log file location too?

SELECT [name],filename
FROM master.dbo.sysdatabases

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-08 : 04:23:42

how about:

select sa.[name],sa.[filename]
from sysaltfiles sa inner join sysdatabases sd
on sa.dbid = sd.dbid
where sd.name = 'RG_SCrap_Pad'

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-08 : 04:25:08

or even:

select sa.[name],sa.[filename]
from sysaltfiles sa

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Glockenspiel
Yak Posting Veteran

50 Posts

Posted - 2004-07-08 : 15:42:38

This might also get you there:

EXEC sp_msforeachdb 'USE ? EXEC sp_helpfile'

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2004-07-08 : 19:27:29

select sa.[name],sa.[filename]
from sysaltfiles sa inner join sysdatabases sd
on sa.dbid = sd.dbid
where sd.name = 'RG_SCrap_Pad'

- only returned the temdb log and mdf file?

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-09 : 03:18:51

Hi Matt,

that's kinda bizarre. It returned the following for me:


DBName                                   DBFilename                                                   
---------------------------------------- ------------------------------------------------------------ 
RG_Scrap_Pad_Data                        c:\data\RG_Scrap_Pad.mdf                                    
RG_Scrap_Pad_Log                         c:\Log\RG_Scrap_Pad.ldf

((I did change it to take left 40 and left 60 to make it more readable))

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2004-07-18 : 20:04:22

im on SQL 7.0 - thats probably the reason.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources