list database and related table in Teradata

[luca90]

I need a code in VB6 to list all database and related table is possible?
I prefere adodb or other to have the result.
Tks.

[SamOscarBrown]

If you are looking for something that lists the database names and the tables for each database. Yes. If that is what you want, I'll send an example.

[luca90]

If you are looking for something that lists the database names and the tables for each database. Yes. If that is what you want, I'll send an example.

Exactlly that!
Send me, now or post in the forum.
Tks

[SamOscarBrown]

Here is how to find the tables names:

Private Sub Command1_Click()
Dim tbl As New ADOX.Table
Dim cat As New ADOX.Catalog
Dim adoConn As New ADODB.Connection
Dim strConString$
    strConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\myDB.mdb;"
    adoConn.Open strConString
    
    Set cat.ActiveConnection = adoConn
    For Each tbl In cat.Tables
        'exclude system tables
        If InStr(1, LCase(tbl.Name), "msys") = 0 Then
            List1.AddItem tbl.Name
        End If
    Next tbl
    
    adoConn.Close
    Set adoConn = Nothing
    Set cat = Nothing
    Set tbl = Nothing
End Sub

Field names examples follows in a bit

[SamOscarBrown]

And you can use this example to find the field names of each table (run it as many times as you have tables---could put in a loop)

Private Sub Command2_Click() 'Get table field names
List2.Clear
 Dim rs As New Recordset
    Set cnn = New ADODB.Connection
      With cnn
       .Provider = "Microsoft.Jet.OLEDB.4.0"
       .ConnectionString = "User ID=Admin;password=;" & " Data Source=" & App.Path & "\myDB.mdb;"
       .CursorLocation = adUseClient
       .Open
      End With
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = cnn
    cmd.CommandText = "select * from test"
    Set rs = cmd.Execute
    For X = 0 To rs.Fields.Count - 1
       List2.AddItem (rs.Fields(X).Name)
    Next X
End Sub

[SamOscarBrown]

I didn't close all in second example when completed....but you should...:-)

[luca90]

I didn't close all in second example when completed....but you should...:-)

Hummmmm....
but i need code for Teradata not for Access mdb.

[SamOscarBrown]

Lo siento.....mea culpa....sorry.....I don't know of Teradata.....what have you found on Google/Bing?

[gibra]

Hummmmm....
but i need code for Teradata not for Access mdb.

The sample code posted by SamOscarBrown is right, because it's 'indipendent' from database you use.
The only thing you have to change is the ConnectionString and the Provider parameters.

For more info about Teradata database see here:
http://www.connectionstrings.com/teradata

[luca90]

Here is how to find the tables names:

Private Sub Command1_Click()
Dim tbl As New ADOX.Table
Dim cat As New ADOX.Catalog
Dim adoConn As New ADODB.Connection
Dim strConString$
    strConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\myDB.mdb;"
    adoConn.Open strConString
    
    Set cat.ActiveConnection = adoConn
    For Each tbl In cat.Tables
        'exclude system tables
        If InStr(1, LCase(tbl.Name), "msys") = 0 Then
            List1.AddItem tbl.Name
        End If
    Next tbl
    
    adoConn.Close
    Set adoConn = Nothing
    Set cat = Nothing
    Set tbl = Nothing
End Sub

Field names examples follows in a bit

Tested and it work fine.
But this code list tha all tables... i need to list also the database into the Teradata server.....(!)

[SamOscarBrown]

If you know (and you should, but I do not) the extension used for a teradata database (like it is .mdb for some versions of MS Access), you can do a IF FILEEXISTS check on the server. Plenty of examples of how to use fileexists on this forum and on GOOGLE.

[gibra]

Tested and it work fine.
But this code list tha all tables... i need to list also the database into the Teradata server.....(!)

This is depend by how database are stored.
Teradata is probably different from SQL Server, Oracle, and so on...
Therefore, you have to get this information on Teradata web site and/or related forum, support, ...
just search
http://forums.teradata.com/forum/dat...ts-in-teradata



Also, you can read the database schema (not only tables) without ADOX but with ADODB only:
ADO OpenSchema Method
http://www.w3schools.com/ado/met_conn_openschema.asp