Appendix B:  Database Schema Information

 

SCHEMA <COLUMNS [TABLE_CATALOG [TABLE_SCHEMA [TABLE_NAME [COLUMN_NAME]]]] | TABLES [TABLE_CATALOG [TABLE_SCHEMA [TABLE_NAME [TABLE_TYPE]]]] >

 

Where

TABLE_CATALOG:    catalog name filter, NULL means all catalog, error if is not null when against Thunderbolt database (EIQ Index)

TABLE_ SCHEMA:     table schema name filter, NULL means all schema

            TABLE_ NAME:         table name filter, NULL means all table

            TABLE_ TYPE:           table type name filter, NULL means all table type, only support "TABLE"

COLUMN_NAME:      column name filter, NULL means all column

 

The schema command is used to query an open database regarding its structure.  Execute each of the schema commands in the same manner as a SQL or TQL command. 

 

SCHEMA COLUMNS [TABLE_CATALOG [TABLE_SCHEMA [TABLE_NAME [COLUMN_NAME]]]]

TABLE_CALALOT, TABLE_SCHEMA, TABLE_NAME and COLUMN_NAME are optional parameters; if left off the default behavior is to return all column definitions in the database.  The result set is defined as follows:

 

ColumnName	Data Type	Length	Comments
TABLE_CATALOG	CHAR	20	Unused
TABLE_SCHEMA	CHAR	20	Unused
TABLE_NAME	CHAR	64
COLUMN_NAME	CHAR	64
COLUMN_GUID	CHAR	40	Unused
COLUMN_PROPID	INT	4	Unused
ORDINAL_POSITION	INT	4	zero based index
COLUMN_HASDEFAULT	SMALL INT	2	FALSE
COLUMN_DEFAULT	CHAR	40	Unused
COLUMN_FLAGS	INT	4	Unused
IS_NULLABLE	SMALL INT	2	Unused
DATA_TYPE	SMALL INT	2	See notes
TYPE_GUID	CHAR	64	Unused
CHARACTER_MAXIMUM_LENGTH	INT	4
CHARACTER_OCTET_LENGTH	INT	4	Unused
NUMERIC_PRECISION	SMALL INT	2
NUMERIC_SCALE	SMALL INT	2
DATETIME_PRECISION	INT	4	Unused
CHARACTER_SET_CATALOG	CHAR	40	Unused
CHARACTER_SET_SCHEMA	CHAR	40	Unused
CHARACTER_SET_NAME	CHAR	40	Unused
COLLATION_CATALOG	CHAR	40	Unused
COLLATION_SCHEMA	CHAR	40	Unused
COLLATION_NAME	CHAR	40	Unused
DOMAIN_CATALOG	CHAR	40	Unused
DOMAIN_SCHEMA	CHAR	40	Unused
DOAMIN_NAME	CHAR	40	Unused
DESCRIPTION	CHAR	40	Unused
KEYINFO	INT	4	See notes
ALIAS_LEVEL	INT	4
UNIQUENESS	INT	4

 

Data types returned via the SCHEMA COLUMNS command are:

Data type value	Symbolic	Value
Numeric	DBTYPE_NUMERIC	131
Date	DBTYPE_DBDATE	133
Char	DBTYPE_STR	129
Small int	DBTYPE_I2	2
Int	DBTYPE_I4	3
Real	DBTYPE_R4	4
Float	DBTYPE_R8	5
Wide String	DBTYPE_WSTR	130

 

 

Key Info is a Bit mask (or combined) with the following values:

Symbolic	Value
TBKEY_IS_NOT_KEYED	0x00000000
TBKEY_IS_KEYED	0x00000001
TBKEY_REALTIME	0x00000002

 

Example:  Realtime key value would be 0x00000001 || 0x00000002 == 0x00000003

 

SCHEMA TABLES [TABLE_CATALOG [TABLE_SCHEMA [TABLE_NAME [TABLE_TYPE]]]]

TABLE_CALALOT, TABLE_SCHEMA, TABLE_NAME and TABLE_TYPE are optional parameters; if left off the default behavior is to return all table definitions in the database.  The result set is defined as follows

 

Column Name	Data Type	Length	Comments
TABLE_CATALOG	CHAR	128	Unused
TABLE_SCHEMA	CHAR	128	Unused
TABLE_NAME	CHAR	128
TABLE_TYPE	CHAR	30	Value == TABLE
TABLE_GUID	CHAR	16	Unused
DESCRIPTION	CHAR	1	Unused
TABLE_PROPID	INT	4	Unused
DATE_CREATED	DATE	8	Unused
DATE_MODIFIED	DATE	8	Unused
REC_LEN	INT	4	Aggregate length of columns
MAX_RECS	INT	4	Record limit
LOADED_RECS	INT	4	Records loaded (includes deleted records)

 

SCHEMA INDEXES

Schema Indexes command returns information about the keyed/indexed columns that are available for the current schema. It will not display information for any non-indexed fields. It gives details such as the schema name, table name, column name, cardinality and key qualifiers.

 

A simplified syntax: 

 

Schema Indexes <CatalogName> <SchemaName> <IndexName> <OLE-DB Index Type> <TableName> <Key Modifier>.

 

A 'NULL' value for an argument indicates that it will not be filtered for specific values.

 

Key Qualifiers:

"KEY DISCRETE"                              - Non-Virtual Key

"VIRTUAL KEY DISCRETE"              - Virtual-Key

"REALTIME"                                       - Real-time Key