SQL Tables

[jsnulf]

I need to enumerate the tables and fields in a SQL server 7.x database which I connect to thru ODBC.  I have little support from my DBA so stored procedures are out of the question.  Is there any way to do this with SQL or VB / VBA?

 

[jsnulf]

From jsnulf... I found answer using ADO instead of DAO....<br><br>This example uses the OpenSchema method to display the name and type of each table in the Pubs database.<br><br>Public Sub OpenSchemaX()<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Dim cnn1 As ADODB.Connection<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim rstSchema As ADODB.Recordset<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim strCnn As String<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Set cnn1 = New ADODB.Connection<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strCnn = &quot;Provider=sqloledb;&quot; & _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&quot;Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;cnn1.Open strCnn<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rstSchema = cnn1.OpenSchema(adSchemaTables)<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Do Until rstSchema.EOF<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Debug.Print &quot;Table name: &quot; & _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rstSchema!TABLE_NAME & vbCr & _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&quot;Table type: &quot; & rstSchema!TABLE_TYPE & vbCr<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rstSchema.MoveNext<br>&nbsp;&nbsp;&nbsp;&nbsp;Loop<br>&nbsp;&nbsp;&nbsp;&nbsp;rstSchema.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;cnn1.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>End Sub<br><br>