list databases and files

[timscronin]

I knew how to do this in sql 2000, is there a script that would show all databases and their file locations on a sql 2005 server

 

[Catadmin]

How'd you work it in 2000? I've always done a "Use <db> Select * from sysfiles"



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"

 

[timscronin]

actually did a simple cursor to go through dbnames, trying to fix for 2005, I thought there was an undocumented view for this

 

[gmmastros]

You could try this (undocumented) script that I just created.

Create Table #Temp
  (FieldId Int, 
  GroupId Int, 
  Size Int, 
  maxSize int, 
  growth int, 
  status int, 
  perf int, 
  name varchar(1000), 
  filename varchar(1000))

Insert Into #Temp
exec sp_msforeachdb 'Select * from [?]..sysfiles'

Select * From #Temp

Drop Table #Temp

-George

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

 

[timscronin]

Thanks does the same as my cursor I just finished, but being a DBA THOU SHALL NOT USE CURSORS

 

[ptheriault]

Tim,
This will work for 2000 or 2005.

sp_msforeachdb 'select * from ?..sysfiles'

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[timscronin]

Thanks same idea works, but you will need to make it
[?]. if you don't do the brackets some weird named db's (like all the db's ahrepoint creates with . and _ will not work

 

[Catadmin]

HA! I found it! Cursor free code!

Use Master

GO

Select db.Name, db.Database_ID, mf.Name, mf.physical_name
from sys.databases db
join sys.Master_files mf
on db.Database_ID = mf.Database_ID
where db.Source_Database_ID is NULL
Order by db.Name

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"

 

[gmmastros]

Tim,

sp_msforeachdb uses a cursor behind the scenes anyway.

Wanna see?

In SQL 2000, you need to Use Master before running this...

[tt][blue]
sp_helptext sp_msforeachdb
[/blue][/tt]

Which uses sp_msforeach_worker, so...
[tt][blue]
sp_helptext sp_msforeach_worker
[/blue][/tt]

-George

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