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...
> > 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
> >
> >
>|||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...
> > 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...
> > > 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
> > >
> > >
> >
> >
>