List of Table names

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

pandkyonAsked:
Who is Participating?
 
ChetOS82Connect With a Mentor Commented:
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
 
ChetOS82Connect With a Mentor Commented:
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
 
pandkyonAuthor Commented:
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
 
ChetOS82Commented:
I am not sure either, just see what happens if you issue it.
0
 
pandkyonAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.