How to get list of all databases on a SQL Server?

  • Thread starter Thread starter J. Ptak
  • Start date Start date
J

J. Ptak

Can anyone tell me how to query a SQL Server for a list of
the databases hosted by that server?

Also, is there a method in the .Net Framework to query the
network for a list of SQL Servers that are available?

Thanks!
 
Hi,

Here you go:
oleDbConnection1.Open();

DataTable schemaTable =

oleDbConnection1.GetOleDbSchemaTable(OleDbSchemaGuid.Catalogs,

new object[] {});

oleDbConnection1.Close();
 
Thanks for the quick reply.

Is there any way to do this with a SqlConnection object
instead of the oleDbConnection object? I have a class that
wraps around the SqlClient namespace to provide custom
methods & properties.

I didn't see any methods close to GetDbSchemaTable in the
SqlConnection object.
-----Original Message-----
Hi,

Here you go:
oleDbConnection1.Open();

DataTable schemaTable =

oleDbConnection1.GetOleDbSchemaTable(OleDbSchemaGuid.Catalogs,

new object[] {});

oleDbConnection1.Close();


J. Ptak said:
Can anyone tell me how to query a SQL Server for a list of
the databases hosted by that server?

Also, is there a method in the .Net Framework to query the
network for a list of SQL Servers that are available?

Thanks!
Click to expand...


.
Click to expand...
 
Hi,

No, no such method.
You will have to:
select * from information_schema.schemata

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

J. Ptak said:
Thanks for the quick reply.

Is there any way to do this with a SqlConnection object
instead of the oleDbConnection object? I have a class that
wraps around the SqlClient namespace to provide custom
methods & properties.

I didn't see any methods close to GetDbSchemaTable in the
SqlConnection object.
-----Original Message-----
Hi,

Here you go:
oleDbConnection1.Open();

DataTable schemaTable =

oleDbConnection1.GetOleDbSchemaTable(OleDbSchemaGuid.Catalogs,

new object[] {});

oleDbConnection1.Close();


J. Ptak said:
Can anyone tell me how to query a SQL Server for a list of
the databases hosted by that server?

Also, is there a method in the .Net Framework to query the
network for a list of SQL Servers that are available?

Thanks!
Click to expand...


.
Click to expand...
Click to expand...
 
No offense, but you should avoid direct system table access.
See my solution - it is more error prone in future as system tables
structure may change...
 
J. Ptak said:
Can anyone tell me how to query a SQL Server for a list of
the databases hosted by that server?

Also, is there a method in the .Net Framework to query the
network for a list of SQL Servers that are available?
Click to expand...

It was a while ago that I did this, but SqlBuddy lists databases using the
following query:

select dbs.name,
dbs.dbid,
dbs.version,
dbs.filename,
dbs.cmptlevel,
cast(dbs.crdate as varchar(50)) as crdate,
HAS_DBACCESS( dbs.name ) as HasAccess
from master.dbo.sysdatabases dbs
order by dbs.name

Note that originally I wanted to use the standards compliant
INFORMATION_SCHEMA views, but I think this produced problems where some of
my user accounts on certain servers didn't have select privalidges against
these views, so therefore the queries produced errors. The query I *wanted*
to use was this:

select dbs.name,
dbs.dbid,
dbs.version,
dbs.filename,
dbs.cmptlevel,
schem.schema_owner as owner,
cast(dbs.crdate as varchar(50)) as crdate,
HAS_DBACCESS( dbs.name ) as HasAccess
from master.dbo.sysdatabases dbs
inner join INFORMATION_SCHEMA.SCHEMATA schem on
schem.catalog_name = dbs.name
order by dbs.name

As someone else pointed out, you may be better off avoiding sysdatabases
because of future compatibility issues.

Hope this helps,

Tobes

p.s - SqlBuddy is open source, source available through
http://sqlbuddy.sourceforge.net
 
Ibrahim,

Good to come back around and find you here (I've been off for a while, busy
on a book). Hope all is well with you.

sp_databases is better than hitting the systables directly, but you can also
get this through INFORMATION_SCHEMA.SCHEMATA.

Kathleen
 
Back
Top