Enumerate all running databases

Question

I'm writing a little Database administration program. It works fine if you give the db, but not when you don't know which db is installed.

How can I enumerate all running databases?

e.g. Output of the program:

Port xy MS-SQL Server 2005
Port ab Postgre SQL Server 
Port cd MySQL Server
Port ef MS-SQL 2008 Express Server
Port gh Oracle Server

Answer 1

For SQL Server you can simply execute a procedure

exec SP_HelpDB

Note that this will only list all databases on the server that you have access to.

If you want all databases regardless of your access, you can do this:

SELECT NAME FROM master..sysdatabases

Answer 2

You will need to query all known database ports to find out if there is an instance running. Where the database is running on a non-standard port, you will probably not be able to find it.

Answer 3

For enumerating sql server instances (which is what i think you mean) you can find various examples on how to do this, which rely on the Sql Server Browser service, the other way is using SQLDMO.

from MSDN:

using System.Data.Sql;

class Program
{
  static void Main()
  {
    // Retrieve the enumerator instance and then the data.
    SqlDataSourceEnumerator instance =
      SqlDataSourceEnumerator.Instance;
    System.Data.DataTable table = instance.GetDataSources();

    // Display the contents of the table.
    DisplayData(table);

    Console.WriteLine("Press any key to continue.");
    Console.ReadKey();
  }

  private static void DisplayData(System.Data.DataTable table)
  {
    foreach (System.Data.DataRow row in table.Rows)
    {
      foreach (System.Data.DataColumn col in table.Columns)
      {
        Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
      }
      Console.WriteLine("============================");
    }
  }
}

If your looking for more then this, i.e. being able to detect mysql/oracle ect. across the network then a more general tool such as nmap may be more appropriate.

Answer 4

This is a re-hash of the previous poster's answer, but in VB.

You want to use the Sql.SqlDataSourceEnumerator.Instance.GetDataSources method of the System.Data.Sql class.

Imports System.Data.Sql

Module Module1
  Sub Main()
    ' Retrieve the enumerator instance and then the data.
    Dim instance As SqlDataSourceEnumerator = SqlDataSourceEnumerator.Instance
    Dim table As System.Data.DataTable = instance.GetDataSources()

    ' Display the contents of the table.
    DisplayData(table)

    Console.WriteLine("Press any key to continue.")
    Console.ReadKey()
  End Sub

  Private Sub DisplayData(ByVal table As DataTable)
    For Each row As DataRow In table.Rows
      For Each col As DataColumn In table.Columns
        Console.WriteLine("{0} = {1}", col.ColumnName, row(col))
      Next
      Console.WriteLine("============================")
    Next
  End Sub
End Module

I've got a database configuration dialog that uses this. Works like a champ.