obtaining physical file and path using T-SQL

SQL 2000.
Is there a way to get a databases physical file names for data and log
using t-sql?
exec sp_helpdb '<database name>' returns two resultsets, the latter
would be good if I can get at the filename in the second resultset. If
anyone knows how?...
sysdatabases seems to hold the physical name for the data only. So
where is the logfile physical name stored?
I need to get the physical names into t-sql variables for later use.
Any help much appreciate.
thanks
PaulDid you look at sp_helpfile?
"Paul Fell" <penzina@.iinet.net.au> wrote in message
news:bea9531b.0308271721.7a2ea54f@.posting.google.com...
> SQL 2000.
> Is there a way to get a databases physical file names for data and log
> using t-sql?
> exec sp_helpdb '<database name>' returns two resultsets, the latter
> would be good if I can get at the filename in the second resultset. If
> anyone knows how?...
> sysdatabases seems to hold the physical name for the data only. So
> where is the logfile physical name stored?
> I need to get the physical names into t-sql variables for later use.
> Any help much appreciate.
> thanks
> Paul|||> I found all the info I needed by looking at sysdatabases and sysaltfiles
Note that the behavior (and existence!) of these tables might change in a
future release, which is why I tend to recommend using system stored
procedures (and information_schema views, where possible) rather than direct
system table access, especially if you're putting code into production.|||Agreed. But if you look at my orginal posting, you'll see I did try
sp_helpfile.
This system SP returns two recordsets. How do I get at the second
records set in my SP? I need to be able to access this data and I
don't know of a way of doing it dynamically.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message news:<#FImIRWbDHA.2580@.TK2MSFTNGP09.phx.gbl>...
> > I found all the info I needed by looking at sysdatabases and sysaltfiles
> Note that the behavior (and existence!) of these tables might change in a
> future release, which is why I tend to recommend using system stored
> procedures (and information_schema views, where possible) rather than direct
> system table access, especially if you're putting code into production.|||> This system SP returns two recordsets. How do I get at the second
> records set in my SP?
No can do. INSERT ... EXEC only picks up the first resultset. Either pick it up in a client program,
or use the source code for the proc to write your own proc.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Paul Fell" <penzina@.iinet.net.au> wrote in message
news:bea9531b.0308281845.730c942b@.posting.google.com...
> Agreed. But if you look at my orginal posting, you'll see I did try
> sp_helpfile.
> This system SP returns two recordsets. How do I get at the second
> records set in my SP? I need to be able to access this data and I
> don't know of a way of doing it dynamically.
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:<#FImIRWbDHA.2580@.TK2MSFTNGP09.phx.gbl>...
> > > I found all the info I needed by looking at sysdatabases and sysaltfiles
> >
> > Note that the behavior (and existence!) of these tables might change in a
> > future release, which is why I tend to recommend using system stored
> > procedures (and information_schema views, where possible) rather than direct
> > system table access, especially if you're putting code into production.