How to get file path of datafiles etc

I know I can use sp_helpdb <db name> to get information about a database.
This SP gives me 2 result sets - one with name, owner, size etc and one with
a list of the files that make up the db.
What I want to do is get the filename column of the second result set into a
variable in my script. Is there a way I can do this ? Maybe use a cursor
in a way I don't know that allows me to specify that it should use the
second result set ?
TIA
SteveSee sp_helpfile in SQL Server Books Online. You could save the results of
this proc to a temporary table, using the INSERT...EXEC syntax.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Steve W" <lsl@.btconnect.com> wrote in message
news:uFi%23DyvaEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I know I can use sp_helpdb <db name> to get information about a database.
> This SP gives me 2 result sets - one with name, owner, size etc and one
with
> a list of the files that make up the db.
> What I want to do is get the filename column of the second result set into
a
> variable in my script. Is there a way I can do this ? Maybe use a cursor
> in a way I don't know that allows me to specify that it should use the
> second result set ?
> TIA
> Steve
>|||Thanks Vyas,
That's just what I needed !
Steve
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:e$QDx6vaEHA.1356@.TK2MSFTNGP09.phx.gbl...
> See sp_helpfile in SQL Server Books Online. You could save the results of
> this proc to a temporary table, using the INSERT...EXEC syntax.
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Steve W" <lsl@.btconnect.com> wrote in message
> news:uFi%23DyvaEHA.3664@.TK2MSFTNGP12.phx.gbl...
database.[vbcol=seagreen]
> with
into[vbcol=seagreen]
> a
cursor[vbcol=seagreen]
>|||Hi,
You can also use the below solution. This gives filenames for all the
databases directly.
select substring(db_name(dbid),1,30) as database_name,filename from
sysaltfiles
-- to put the result into a temp table
select substring(db_name(dbid),1,30) as database_name,filename into #temp
from sysaltfiles
select * from #temp
Thanks
Hari
MCDBA
"Steve W" <lsl@.btconnect.com> wrote in message
news:eRxuf9vaEHA.3352@.TK2MSFTNGP12.phx.gbl...
> Thanks Vyas,
> That's just what I needed !
> Steve
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:e$QDx6vaEHA.1356@.TK2MSFTNGP09.phx.gbl...
of[vbcol=seagreen]
> database.
one[vbcol=seagreen]
> into
> cursor
>