List the path for all MDF and LDF files of all databases

I need to list the patch for all MDF and LDF files of all databases on a SQL
Server 2000 instance.
How to do it?

Thanks,

Felipe
Carlos_FelipeAsked:
Who is Participating?
 
BrandonGalderisiConnect With a Mentor Commented:
That will only list the items in master.

This will list all.
select db_name() as 'dbname',* into #Sysfiles from sysfiles
exec sp_msforeachdb 'insert into #sysfiles select ''?'',* from [?]..sysfiles'
select * from #sysfiles
drop table #sysfiles

Open in new window

0
 
Faiga DiegelSr Database EngineerCommented:
select *
FROM master.sys.sysaltfiles
0
 
BrandonGalderisiCommented:
and sysfiles is not owned by sys in 2000.  That is a 2005 query where sysfiles is in the sys schema.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
BrandonGalderisiCommented:
Really?  That's the answer you accept.  It's the wrong answer and it's misleading for future PAQ value.
0
 
BrandonGalderisiCommented:
Ok.  I missed that you were referring to sysaltfiles, not sysfiles.  But it's still not in SQL 2000.
0
 
BrandonGalderisiCommented:
I think that a split is more fair, but http:#22996376 still is not SQL 2000 compatible.
0
 
Faiga DiegelConnect With a Mentor Sr Database EngineerCommented:
do you have SQL 200o edition? Try it under master database, it will work.

SELECT * FROM sysaltfiles
0
 
BrandonGalderisiCommented:
You posted:

select *
FROM master.sys.sysaltfiles
0
 
BrandonGalderisiCommented:
I think that http:#22996432 and http:#23014321 are two correct answers and should be accepted.  The question should SURELY not be deleted.
0
 
almanderCommented:
I agree

I think that http:#22996432 and http:#23014321 are two correct answers and should be accepted.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.