enumerate databases on a server Oracle, SQL Server, etc.

Posted on 2004-08-18
Last Modified: 2013-12-25
I would like to know how to enumerate all databases on a given server, whether it be Oracle, SQL Server, Sybase, MySQL, etc.

I'm familiar with SQLDMO for SQL Server.
Question by:kevinbenedict
  • 3
  • 2
LVL 52

Expert Comment

by:Carl Tawn
ID: 11839030
Well, for SQL Server you can use the following query:

     SELECT * FROM master..sysdatabases

I don't think there is a generic way that works across all database servers.
LVL 29

Expert Comment

ID: 11841425
Ther is no generic way to do this.  Each database server has a different way of keeping track of their own databases.  You would have to setup different functions base on the type of database server used.


Author Comment

ID: 11842475
  "Each database server has a different way of keeping track..."

Does anyone know what those ways are, say of 3 of the common RDBMSes out there? (Oracle, Sybase, MySQL)

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

LVL 29

Expert Comment

ID: 11842633

MySQL: show databases;
Sybase and MS-SQL: SELECT name FROM master..sysdatabases

MS SQL Server database is equivalent to an Oracle tablespace. A tablespace is a logical method of storage. A tablespace consists of one or more physical files. A tablespace can have 0 or more schemas (eg a rollback or temp tablesspace doesn't really have a schema).

Oracle: SELECT tablespace_name FROM dba_tablespaces;


Author Comment

ID: 11884394

   you said: "MySQL: show databases;"

Is "show databases" a command I can execute on an ADO Connection and return a recordset?  How would that work to list the databases in code?

LVL 29

Accepted Solution

leonstryker earned 400 total points
ID: 11884440
>>Is "show databases" a command I can execute on an ADO Connection and return a recordset?  

As far as I understand it, Yes.  I have not worked with MySQL myself, but got this from a reliable source.  Of course you should test it.  You can also post this question in the MySQL area and I am sure they will confirm this or suggest a better method.


Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now