Publish date: 2003-03-21
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