List database objects (tables, columns..) using ADO/ADOX

There are several ways you can get a list of database objects from a database (or ADODB connection).

1. Using ADODB.Connection and OpenSchema method

Sub ListTablesADO()
  Dim Conn As New ADODB.Connection
  Dim TablesSchema As ADODB.Recordset
  Dim ColumnsSchema As ADODB.Recordset

  'Open connection you want To get database objects
  Conn.Provider = "MSDASQL"
  Conn.Open "DSN=...;Database=...;", "UID", "PWD"
  
  'Get all database tables.
  Set TablesSchema = Conn.OpenSchema(adSchemaTables) 
  Do While Not TablesSchema.EOF
    'Get all table columns.
    Set ColumnsSchema = Conn.OpenSchema(adSchemaColumns, _
      Array(Empty, Empty, "" & TablesSchema("TABLE_NAME")))
    Do While Not ColumnsSchema.EOF
      Debug.Print TablesSchema("TABLE_NAME") & ", " & _
        ColumnsSchema("COLUMN_NAME")
      ColumnsSchema.MoveNext
    Loop
    TablesSchema.MoveNext
  Loop
End Sub

TablesSchema fields : TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_GUID, DESCRIPTION, TABLE_PROPID, DATE_CREATED, DATE_MODIFIED
ColumnsSchema fields : TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_GUID, COLUMN_PROPID, ORDINAL_POSITION, COLUMN_HASDEFAULT, COLUMN_DEFAULT, COLUMN_FLAGS, IS_NULLABLE, DATA_TYPE, TYPE_GUID, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME, DESCRIPTION, SS_DATA_TYPE

2. Using ADOX.Catalog and its collections

Sub ListTablesADOX()
  Dim Conn As New ADODB.Connection
  
  'Open connection you want To get database objects
  Conn.Provider = "MSDASQL"
  Conn.Open "DSN=...;Database=...;", "UID", "PWD"
  
  'Create catalog object
  Dim Catalog As New ADOX.Catalog
  Set Catalog.ActiveConnection = Conn
  
  'List tables And columns
  Dim Table As ADOX.Table, Column As ADOX.Column
  For Each Table In Catalog.Tables
    For Each Column In Table.Columns
      Debug.Print Table.Name & ", " & Column.Name
    Next
  Next
End Sub

Table properties : Columns, DateCreated, DateModified, Indexes, Keys, Name, ParentCatalog, Properties, Type
Column properties : Attributes, DefinedSize, Name, NumericScale, ParentCatalog, Precision, Properties, RelatedColumn, SortOrder, Type

Copyright and use this code

The source code on this page and other samples at https://www.motobit.com/tips/ are a free code, you can use it as you want: copy it, modify it, use it in your products, ...

If you use this code, please:

1. Leave the author note in the source.

2. Link this sample from you page.

<A
 Href="https://www.motobit.com/tips/detpg_listdb/"
 Title="This article descibes several ways to
	get a list of database
	objects (tables, columns, indexes, keys,
	...) and its properties using
	ADO and VBA/VBS."
>List database objects (tables, columns..) using ADO/ADOX</A>

List database objects (tables, columns..) using ADO/ADOXMOTOBIT.COM

1. Using ADODB.Connection and OpenSchema method

2. Using ADOX.Catalog and its collections

See also

Copyright and use this code