Table fields enumeration

[LeanneGodney]

Hi guys,

Is there an ADO equivalent to currentdb.tabledefs(1).fields(1).name?

I regularly try to get a list of the fields into a word document with:

For i = 0 To CurrentDb.TableDefs("tblTradesNTG").Fields.Count - 1
objWord.Selection.TypeText CurrentDb.TableDefs("tblTradesNTG").Fields(i).Name & Chr(13)
Next i

 

[cmmrfrds]

This information is stored in the tables sysobjects and syscomments on SQL Server. There is also a schema on SQL Server. Another option is ADOX using a connection to SQL Server. I usually use a query against the sys tables on sql server, but I don't have an example at work with me. If I remember I will look up some example when I get home tonight, but here is a example using ADOX.


Function catalogTC()
'-- set reference to ADOX library
'- Microsoft ADO Ext. 2.6 for DDL and Security
'-- Microsoft ActiveX data objects 2.6 library also needed for ADO

Dim cg As New ADOX.Catalog
Dim tb As New ADOX.Table
Dim cn As ADODB.Connection
Dim cl As Column
Dim pp As Property

Set cg.ActiveConnection = CurrentProject.Connection

For Each tb In cg.Tables
Debug.Print "table name = "; "-------"; tb.Name; "--------"; tb.Type

' If tb.Type = "TABLE" Then
' If tb.Type = "LINK" Then
' For Each pp In tb.Properties
' Debug.Print "property name = "; pp.Name
' Debug.Print "property value = "; pp.Value
' Next

If (tb.Type = "TABLE" And tb.Name = "Categorys") Then
For Each cl In tb.Columns
Debug.Print "name = "; cl.Name
For Each pp In cl.Properties
Debug.Print "property name = "; pp.Name
Debug.Print "property value = "; pp.Value
Next
Next
End If
Next

End Function

 

[cmmrfrds]

As promised, here is a query using the information schema in sql server. These are using the Northwind database.

Select T.Table_type, C.Table_name, C.Column_Name, C.Data_Type
From Northwind.information_schema.tables T
Inner join Northwind.information_schema.columns C ON T.Table_name = C.Table_name
Where T.table_type='BASE TABLE'

Here is a query using the sysobjects and syscolumns tables.

-- Table and columns names in SQL Server
select SO.Id,
TableName = SO.Name,
ColumnName = SC.name
from Sysobjects SO
Inner join Syscolumns SC on SO.id = SC.id
where SO.TYpe = 'U'
--and SO.Name like 'yourtable%'
order by SO.Name