?
Solved

List of Table names

Posted on 2008-11-17
5
Medium Priority
?
174 Views
Last Modified: 2012-05-05
Hello Experts,

I'm trying to connect to various databases and I need to pull back the  table names after I connect.  I am able to identify the database type ( Oracle, DB2,  MSSQL & Access) before i connect based on the driver name I capture in a variable.  Once conncected,  I need the appropriate SQL string to be sent to the database based on the database type.

What would be the best way to accomplish this.

PSEDUOCODE:

if driver.text = Oracle then
sqlstring=("SELECT .... FROM.....'")

if driver.text = IBM DB2 then
sqlstring=("SELECT .... FROM.....'")

if driver.text = Access then
sqlstring=("SELECT .... FROM.....'")

if driver.text =MSSQL then
sqlstring=("SELECT .... FROM.....'")

The snippet I use below works fine for an Oracle database.

 oODBCConnection = New Odbc.OdbcConnection(sConnString)
 oODBCConnection.Open()
 Dim DA As New Odbc.OdbcDataAdapter("SELECT table_name from Tabs ORDER BY Table_Name;",  _ oODBCConnection)

Thanks in advance

Pandkyon

0
Comment
Question by:pandkyon
  • 3
  • 2
5 Comments
 
LVL 18

Accepted Solution

by:
ChetOS82 earned 1600 total points
ID: 22979120
For SQL Server it is: SELECT name FROM sysobjects WHERE xtype = 'U'

For Access it is: SELECT Name FROM MSysObjects WHERE type = 1 AND Flags = 0

0
 
LVL 18

Assisted Solution

by:ChetOS82
ChetOS82 earned 1600 total points
ID: 22979134
I think it is simply "list tables" in DB2, but I am not sure.  I don't have access to a DB2 server to verify.
0
 

Author Comment

by:pandkyon
ID: 22979331
ChetOS82:
I will try the two suggestions for SQL and Access.  I 'm not quite sure how to use the "List Table" in an sql statement. could you elaborate?

thanks!

Pandkyon
0
 
LVL 18

Expert Comment

by:ChetOS82
ID: 22979344
I am not sure either, just see what happens if you issue it.
0
 

Author Comment

by:pandkyon
ID: 22985652
ChetOS82:,  Your solutions plus the snippet below works for all the dbs I have access to.  Points awarded.  Thanks,
Pandkyon


Dim DA As New Odbc.OdbcDataAdapter("SELECT * FROM SYSIBM.SYSTABLES TAB;", oODBCConnection)

Open in new window

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

850 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