List of all databases with their data & log files?

Is there an easy way to print out a simple text report of
all database names plus their internal database filenames
& logfile names plus the actual physical locations of each
file in SQL Server 2000?
I'm an Oracle admin who's just inherited an SQL Server
full of multiple databases and I need to make a structural
diagram of what all lives where inside this server. In
Oracle, a simple sql script dumps out a text list of all
these kinds of things, but all I've been able to discover
thus far in MS SQL Enterprise Manager is an unfriendly GUI
interface that makes you have to repeatedly point, click,
and browse many times over and over again and again to get
this info one tiny piece at a time, which isn't very
efficient.
New to MS SQL wrote:
> Is there an easy way to print out a simple text report of
> all database names plus their internal database filenames
> & logfile names plus the actual physical locations of each
> file in SQL Server 2000?
> I'm an Oracle admin who's just inherited an SQL Server
> full of multiple databases and I need to make a structural
> diagram of what all lives where inside this server. In
> Oracle, a simple sql script dumps out a text list of all
> these kinds of things, but all I've been able to discover
> thus far in MS SQL Enterprise Manager is an unfriendly GUI
> interface that makes you have to repeatedly point, click,
> and browse many times over and over again and again to get
> this info one tiny piece at a time, which isn't very
> efficient.
Try this:
exec sp_MSforeachDB "sp_helpdb ?"
sp_helpdb by itself will give you basic database information for all
databases
sp_helpfile will give you the files used in the currently selected
database
Passing a database name to sp_helpdb gives both results and the
sp_MSforeachDB undocumented stored procedure automatically iterates
through the list of databases on the server and generates multiple
results sets.
David G.
|||Before you start getting bent out of shape over SQL Server, there an easy way
to accompish this. :-)
Open up Query Analyzer, select the Master database and type in the following
query:
select name, filename from sysdatabases
This will give you a quick list of all the database on the SQL Server
machine adn their physical location. If you want more info, let me know and
we can go from there.
Scott
"New to MS SQL" wrote:

> Is there an easy way to print out a simple text report of
> all database names plus their internal database filenames
> & logfile names plus the actual physical locations of each
> file in SQL Server 2000?
> I'm an Oracle admin who's just inherited an SQL Server
> full of multiple databases and I need to make a structural
> diagram of what all lives where inside this server. In
> Oracle, a simple sql script dumps out a text list of all
> these kinds of things, but all I've been able to discover
> thus far in MS SQL Enterprise Manager is an unfriendly GUI
> interface that makes you have to repeatedly point, click,
> and browse many times over and over again and again to get
> this info one tiny piece at a time, which isn't very
> efficient.
>
|||> select name, filename from sysdatabases
... and if on 2000, you can join sysaltfiles to get sizing information...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SQLScott" <SQLScott@.discussions.microsoft.com> wrote in message
news:CC55215A-B041-4370-8D87-8C4ABDBB0072@.microsoft.com...[vbcol=seagreen]
> Before you start getting bent out of shape over SQL Server, there an easy way
> to accompish this. :-)
> Open up Query Analyzer, select the Master database and type in the following
> query:
> select name, filename from sysdatabases
> This will give you a quick list of all the database on the SQL Server
> machine adn their physical location. If you want more info, let me know and
> we can go from there.
> Scott
> "New to MS SQL" wrote: