Getting List of Databases From Server

Is there a way I can query the database server (SQL 2000) to get a list of all the available databases? As a threshold problem I am not clear how I would connect to the server. I am using VB.Net and presently connect to one database at a time. The connection string is in an XML file, such as:

<?xml version="1.0" encoding="utf-8" ?>

<Connection>workstation id=ASUS;packet size=4096;user id=sa;data source="5.15.183.222\KWMSDE2"; persist security info=True;initial catalog=tsLocal;password=pass

</Connection>

Is there some special way that you connect to the server without specifying the database?

Is there a certain query I must use once I am connected? Can the information be returned in the form of a data table in a data set?

Thanks.

Hi Kevin.

You can run the following query to get a list of databases on the given server:

select * from master.dbo.sysdatabases

You'll want to investigate some of the options/values available when using the databaseproperty() and databasepropertyex() functions to ensure the given database is available for connections (for example, it needs to be online)...

As for connecting to the server without specifying a database, you'll always be connected to a given database even if you don't specify one (your default database), but you'd be safe always connecting to one of the system db's if you have the permissions (master,model,msdb,tempdb).

HTH,

|||

Hi,

you can also use the SMO class library to evaluate the databases on the server. You will get a typed object back instead of just a datarow. THe object holds many properties the database has, so there would be no need to use additional queries for getting information about the database. A quick like would be something like this in C# using the SMo libaries:

Server s = new Server(@."Servername\InstanceName");
foreach(Database d in s.Databases) //Holds the database collection
{
Debug.Writeline(d.Name);
}

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de