Database metadata extract (VB)

Option Explicit

'the following samples demonstrate how to use
'OLE DB Schemata with the Interbase Provider for
'extract metadatas from Database

'******************************************************************
'source text from file employee.ibp
'
'data source=c:\Base\Employee.gdb;
'ctype=win1251;
'user id=sysdba;
'password=masterkey;
'array_vt_type=true;
'auto_commit=false;
'force_param_describe=false;
'******************************************************************

'object Connection
Private con As ADODB.Connection
'object Recordset
Private rs As ADODB.Recordset

'max records per worksheet
'0 - print all records
Private Const MaxRecords As Long = 50

'workbook for data
Private work_bk As Workbook

'extract all metadata for employee.gdb
'use methods CreateReport and GetTypeName
Sub Sample10_1()

 'create new book
 Set work_bk = Application.Workbooks.Add

 'open connection
 Set con = New ADODB.Connection
 con.Open "file name=" & ThisWorkBook.Path & "\employee.ibp"
 con.BeginTrans

 'physical schemes
 'provider types
 Call MOpenSchema(adSchemaProviderTypes, "PROVIDER_TYPES")
 'indexes
 Call MOpenSchema(adSchemaIndexes, "INDEXES")

 'logical schemes
 'SCHEMA TABLES - extract tables from db
 Call MOpenSchema(adSchemaTables, "TABLES")
 'constraints
 Call MOpenSchema(adSchemaTableConstraints, "TABLE_CONSTRAINTS")
 'Primary Keys
 Call MOpenSchema(adSchemaPrimaryKeys, "PRIMARY_KEYS")
 'Foreign Keys
 Call MOpenSchema(adSchemaForeignKeys, "FOREIGN_KEYS")
 'Check
 Call MOpenSchema(adSchemaCheckConstraints, "CHECK_CONSTRAINTS")
 'Referential Constraints
 Call MOpenSchema(adSchemaReferentialConstraints, "REFERENTIAL_CONSTRAINTS")

 'constraints by tables
 Call MOpenSchema(adSchemaConstraintTableUsage, "CONSTRAINT_TABLE_USAGE")
 'constraints by columns
 Call MOpenSchema(adSchemaConstraintColumnUsage, "CONSTRAINT_COLUMN_USAGE")
 'keys column usage
 Call MOpenSchema(adSchemaKeyColumnUsage, "KEY_COLUMN_USAGE")

 'columns
 Call MOpenSchema(adSchemaColumns, "COLUMNS")

 'priveleges
 Call MOpenSchema(adSchemaTablePrivileges, "TABLES_PRIVILEGES")
 Call MOpenSchema(adSchemaColumnPrivileges, "COLUMN_PROVILEGES")

 'views
 Call MOpenSchema(adSchemaViews, "VIEWS")
 Call MOpenSchema(adSchemaViewTableUsage, "VIEW_TABLE_USAGE")
 Call MOpenSchema(adSchemaViewColumnUsage, "VIEWS_COLUMN_USAGE")

 'domains
 Call MOpenSchema(adSchemaColumnsDomainUsage, "COLUMN_DOMAIN_USAGE")

 'stored procedures
 Call MOpenSchema(adSchemaProcedureParameters, "PROCEDURE_PARAMETERS")
 Call MOpenSchema(adSchemaProcedures, "PROCEDURES")
 Call MOpenSchema(adSchemaCharacterSets, "CHARACTER_SETS ")
 Call MOpenSchema(adSchemaCollations, "COLLATIONS")

 'keywords
 Call MOpenSchema(adSchemaDBInfoKeywords, "DBINFO_KEYWORDS")

 'literals
 Call MOpenSchema(adSchemaDBInfoLiterals, "DBINFO_LITERALS")

con.CommitTrans
con.Close

End Sub 'Sample10_1