Tables in DB

How do I find out how many tables there are in a database using SQL or ADO????

Thanx,
Squirrelly1

This will list them, will that help?

VB Code:

Private Sub GetDbTables()
 
   Dim Cnxn As ADODB.Connection
   Dim rstSchema As ADODB.Recordset
   Dim strCnxn As String
      
   Set Cnxn = New ADODB.Connection
   strCnxn = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=Pubs;User Id=sa;Password=; "
   Cnxn.Open strCnxn
      
   Set rstSchema = Cnxn.OpenSchema(adSchemaTables)
   
   Do Until rstSchema.EOF
      List1.AddItem "Table name: " & rstSchema!TABLE_NAME & vbCr & "Table type: " & rstSchema!TABLE_TYPE & vbCr
      rstSchema.MoveNext
   Loop
   
   ' clean up
   rstSchema.Close
   Cnxn.Close
   Set rstSchema = Nothing
   Set Cnxn = Nothing
   
End Sub

Is there any way to show just the tables where the table type is "TABLE". All of the other tables are irrelavent...


thanx again,
Squirrelly1:p

actually, I need to be able to loop through them all and pull data from them based on a criteria string in sql...

This is some cool code, but now that I think of it, it won't really help me.


Do you know how to do what I need?

squirrelly1

Well, I never had the need to do this before, but I should work.

Modify the code I gave you and use rstSchema!TABLE_TYPE = 'Whatever'