Query Data File Names From Data Dictionary

[gharabed]

Is there a view or table in master where all of the data files in all of the databases can be queried? For example, if database A is composed of "D:\data\A.ndf" and "D:\data\A2.ndf" and "E:\data\A3.ndf" is there a view/table that I can write a SQL query against to get a listing of those files?

 

[gmmastros]

This isn't exactly what you asked, but it may work for you...

select * from sysfiles

To do this for all your databases, you can use the undocumented feature called ms_msforeachdb.

Create Table #Temp(DatabaseName VarChar(255), LogicalName VarChar(255), FileName VarChar(1000))

exec sp_msforeachdb '
		Insert 
		Into 	#Temp(DatabaseName, LogicalName, FileName) 
		Select  ''?'', Name, FileName 
		from    [?]..sysfiles'

Select * From #Temp

Drop Table #Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Catadmin]

There isn't a real system view that shows it for every DB, because usually the file names are contained within the DB itself. So George's solution is probably the best, and quickest, solution to get your information without writting a cursor or some such thing.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"