using sp_msforeachdb to select data from a table within each db

[divinyl]

Hi all

Basically what i would like to do is find out the associated logical filenames for each database, within one statement. if i wanted to do this for one statement, i would select the database within query analyzer and run select * from sysfiles. I need to do this for each database on my server. I'm not a pro at this - here's what i've tried and it doesn't work:

sp_msforeachdb @command1 = 'select * from sysfiles'

Unfortunately all it returns is info for the specific database context you are in. I would have thought it would disregard the database context and loop through each database, selecting from the sysfiles system db within each database. But it doesn't!

Could anyone help??

Thanks in advance,
Div

 

[bborissov]

sp_msforeachdb 'select * from ?..sysfiles'

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLBill]

Look in the FAQ for this forum. Look for the header Undocumented Stored Procedures. There is a FAQ on using sp_MSForEachDB

-SQLBill

Posting advice: FAQ481-4875

 

[divinyl]

thanks very much guys - the 'select * from ?..sysfiles' was exactly what i was looking for..useful to know about the undocumented store proc faq too..

Thanks!
Div