| Do you like this article? Please, rate it and write review! Rated: by Aspin.com users |
There are several ways you can get a list of database objects from a database (or ADODB connection).
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
Table properties : Columns, DateCreated, DateModified, Indexes, Keys, Name, ParentCatalog, Properties, Type
Column properties : Attributes, DefinedSize, Name, NumericScale, ParentCatalog, Precision, Properties, RelatedColumn, SortOrder, Type
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
|
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
|
Column properties : Attributes, DefinedSize, Name, NumericScale, ParentCatalog, Precision, Properties, RelatedColumn, SortOrder, Type
See also
for 'List database objects (tables, columns..) using ADO/ADOX' article
Copyright and use this code
The source code on this page and other samples at http://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.
or
2. Link this sample from you page.
<A Href="http://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>