Extracting properties of a database table

This example created by Ohman Automation Corp. (OAC) - www.OhmanCorp.com

Information extracted using OpenSchema(adSchemaColumns) from an ADODB.Connection object, all column properties
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
    Products CategoryID     4 False   122 True 3       10                       Same entry as in Categories table.
    Products Discontinued     10 True =No 90 False 11   2                           Yes means item is no longer available.
    Products ProductID     1 False   90 False 3       10                       Number automatically assigned to new product.
    Products ProductName     2 False   74 False 130   40 80                          
    Products QuantityPerUnit     5 False   106 True 130   20 40                         (e.g., 24-count case, 1-liter bottle).
    Products ReorderLevel     9 True 0 122 True 2       5                       Minimum units to maintain in stock.
    Products SupplierID     3 False   122 True 3       10                       Same entry as in Suppliers table.
    Products UnitPrice     6 True 0 122 True 6       19                        
    Products UnitsInStock     7 True 0 122 True 2       5                        
    Products UnitsOnOrder     8 True 0 122 True 2       5                        

Information extracted from all the Field Properties
BASECOLUMNNAME BASETABLENAME ISAUTOINCREMENT ISCASESENSITIVE COLLATINGSEQUENCE
ProductID Products True False 1033
ProductName Products False False 1033
SupplierID Products False False 1033
CategoryID Products False False 1033
QuantityPerUnit Products False False 1033
UnitPrice Products False False 1033
UnitsInStock Products False False 1033
UnitsOnOrder Products False False 1033
ReorderLevel Products False False 1033
Discontinued Products False False 1033

Information extracted using OpenSchema(adSchemaKeyColumnUsage ) from an ADODB.Connection object, all constraint properties
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME COLUMN_GUID COLUMN_PROPID ORDINAL_POSITION
    CategoriesProducts     Products CategoryID     1
    CategoryName     Categories CategoryName     1
    CustomersOrders     Orders CustomerID     1
    EmployeesOrders     Orders EmployeeID     1
    OrdersOrder Details     Order Details OrderID     1
    PrimaryKey     Categories CategoryID     1
    PrimaryKey     Customers CustomerID     1
    PrimaryKey     Employees EmployeeID     1
    PrimaryKey     MSysIMEXColumns SpecID     1
    PrimaryKey     MSysIMEXColumns FieldName     2
    PrimaryKey     MSysIMEXSpecs SpecName     1
    PrimaryKey     Order Details OrderID     1
    PrimaryKey     Order Details ProductID     2
    PrimaryKey     Orders OrderID     1
    PrimaryKey     Products ProductID     1
    PrimaryKey     Shippers ShipperID     1
    PrimaryKey     Suppliers SupplierID     1
    ProductsOrder Details     Order Details ProductID     1
    ShippersOrders     Orders ShipVia     1
    SuppliersProducts     Products SupplierID     1
    TbIndex     MSysCmdbars TbName     1

Information extracted using OpenSchema(adSchemaReferentialConstraints) from an ADODB.Connection object, all Referential properties
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE DESCRIPTION
    CategoriesProducts     CategoriesProducts FULL NO ACTION NO ACTION  
    CustomersOrders     CustomersOrders FULL CASCADE NO ACTION  
    EmployeesOrders     EmployeesOrders FULL NO ACTION NO ACTION  
    OrdersOrder Details     OrdersOrder Details FULL NO ACTION CASCADE  
    ProductsOrder Details     ProductsOrder Details FULL NO ACTION NO ACTION  
    ShippersOrders     ShippersOrders FULL NO ACTION NO ACTION  
    SuppliersProducts     SuppliersProducts FULL NO ACTION NO ACTION  

Information extracted using OpenSchema(adSchemaForeignKeys) from an ADODB.Connection object, all Referential properties
PK_TABLE_CATALOG PK_TABLE_SCHEMA PK_TABLE_NAME PK_COLUMN_NAME PK_COLUMN_GUID PK_COLUMN_PROPID FK_TABLE_CATALOG FK_TABLE_SCHEMA FK_TABLE_NAME FK_COLUMN_NAME FK_COLUMN_GUID FK_COLUMN_PROPID ORDINAL UPDATE_RULE DELETE_RULE PK_NAME FK_NAME DEFERRABILITY
    Categories CategoryID         Products CategoryID     1 NO ACTION NO ACTION PrimaryKey CategoriesProducts  
    Customers CustomerID         Orders CustomerID     1 CASCADE NO ACTION PrimaryKey CustomersOrders  
    Employees EmployeeID         Orders EmployeeID     1 NO ACTION NO ACTION PrimaryKey EmployeesOrders  
    Orders OrderID         Order Details OrderID     1 NO ACTION CASCADE PrimaryKey OrdersOrder Details  
    Products ProductID         Order Details ProductID     1 NO ACTION NO ACTION PrimaryKey ProductsOrder Details  
    Shippers ShipperID         Orders ShipVia     1 NO ACTION NO ACTION PrimaryKey ShippersOrders  
    Suppliers SupplierID         Products SupplierID     1 NO ACTION NO ACTION PrimaryKey SuppliersProducts  

Information extracted using OpenSchema(adSchemaForeignKeys) from an ADODB.Connection object, all Referential properties
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE TABLE_GUID DESCRIPTION TABLE_PROPID DATE_CREATED DATE_MODIFIED
    Categories TABLE   Categories of Northwind products.   9/13/1995 10:51:30 AM 9/13/1995 10:52:27 AM
    Category Sales for 1995 VIEW   Totals product sales by category based on values returned by the Product Sales for 1995 query.   9/13/1995 10:51:43 AM 8/19/1996 12:26:46 PM
    Current Product List VIEW   Filters records in Products table; query returns only products that are not discontinued.   9/13/1995 10:51:44 AM 8/19/1996 12:30:15 PM
    Customers TABLE   Customers' names, addresses, and phone numbers.   9/13/1995 10:51:32 AM 8/20/1996 11:27:43 AM
    Employees TABLE   Employees' names, titles, and personal information.   9/13/1995 10:51:33 AM 8/20/1996 3:45:24 PM
    Invoices VIEW   (Criteria) Record source for Invoice report. Based on six tables. Includes expressions that concatenate first and last employee name and that use the CCur function to calculate extended price.   9/13/1995 10:51:44 AM 9/16/1996 6:36:29 PM
    MSysACEs SYSTEM TABLE       9/13/1995 10:25:04 AM 9/13/1995 10:25:04 AM
    MSysCmdbars ACCESS TABLE       8/16/1996 10:39:41 AM 8/16/1996 10:39:41 AM
    MSysIMEXColumns ACCESS TABLE       9/13/1995 10:25:06 AM 9/13/1995 10:25:06 AM
    MSysIMEXSpecs ACCESS TABLE       9/13/1995 10:25:06 AM 9/13/1995 10:25:06 AM
    MSysModules ACCESS TABLE       5/24/1996 12:20:17 PM 5/24/1996 12:20:20 PM
    MSysModules2 ACCESS TABLE       9/13/1995 10:25:05 AM 5/24/1996 12:20:17 PM
    MSysObjects SYSTEM TABLE       9/13/1995 10:25:04 AM 9/13/1995 10:25:04 AM
    MSysQueries SYSTEM TABLE       9/13/1995 10:25:04 AM 9/13/1995 10:25:04 AM
    MSysRelationships SYSTEM TABLE       9/13/1995 10:25:04 AM 9/13/1995 10:25:04 AM
    Order Details TABLE   Details on products, quantities, and prices for each order in the Orders table.   9/13/1995 10:51:34 AM 8/20/1996 7:30:21 PM
    Order Details Extended VIEW   Record source for several forms and reports. Uses CCur function to compute the ExtendedPrice for each item ordered.   9/13/1995 10:51:45 AM 9/16/1996 6:24:55 PM
    Order Subtotals VIEW   Record source for other queries. Uses Sum and CCur functions to compute subtotal for each order.   9/13/1995 10:51:45 AM 6/10/1996 6:13:37 PM
    Orders TABLE   Customer name, order date, and freight charge for each order.   9/13/1995 10:51:38 AM 9/13/1995 10:52:28 AM
    Product Sales for 1995 VIEW   Record source for Category Sales for 1995 query. Uses Sum and CCur functions.   9/13/1995 10:51:45 AM 9/16/1996 7:20:41 PM
    Products TABLE   Product names, suppliers, prices, and units in stock.   9/13/1995 10:51:41 AM 9/16/1996 6:22:40 PM
    Products Above Average Price VIEW   (Subquery) Returns products that have a unit price higher than the average.   9/13/1995 10:51:46 AM 9/16/1996 6:42:25 PM
    Quarterly Orders VIEW   Record source for Quarterly Orders form. Lists only customers who had orders in 1995.   9/13/1995 10:51:46 AM 9/16/1996 6:38:40 PM
    Sales by Category VIEW   Record source for Sales by Category report.   9/13/1995 10:51:47 AM 9/16/1996 6:39:33 PM
    Shippers TABLE   Shippers' names and phone numbers.   9/13/1995 10:51:42 AM 9/13/1995 10:52:28 AM
    Suppliers TABLE   Suppliers' names, addresses, phone numbers, and hyperlinks to home pages.   9/13/1995 10:51:42 AM 9/25/1996 1:45:39 PM
    Ten Most Expensive Products VIEW   (Top Values) Returns 10 most expensive products. Specifies the number of highest values to return using the Top Values box on the toolbar.   9/13/1995 10:51:47 AM 9/16/1996 6:44:31 PM