The Structure of Metadata Datasets

From RAD Studio
Jump to: navigation, search

Go Up to Accessing dbExpress Schema Information


For each type of metadata you can access using TSQLDataSet, there is a predefined set of columns (fields) that are populated with information about the items of the requested type.

Information about tables

When you request information about tables (stTables or stSysTables), the resulting dataset includes a record for each table. It has the following columns:

Columns in tables of metadata listing tables :

Column name Field type Contents

RECNO

ftInteger

A record number that uniquely identifies each record.

CATALOG_NAME

ftString

The name of the catalog (database) that contains the table. This is the same as the Database parameter on an SQL connection component.

SCHEMA_NAME

ftString

The name of the schema that identifies the owner of the table.

TABLE_NAME

ftString

The name of the table. This field determines the sort order of the dataset.

TABLE_TYPE

ftInteger

Identifies the type of table. It is a sum of one or more of the following values: 1: Table 2: View 4: System table 8: Synonym 16: Temporary table 32: Local table.



Information about stored procedures

When you request information about stored procedures (stProcedures), the resulting dataset includes a record for each stored procedure. It has following columns:

Columns in tables of metadata listing stored procedures :

Column name Field type Contents

RECNO

ftInteger

A record number that uniquely identifies each record.

CATALOG_NAME

ftString

The name of the catalog (database) that contains the stored procedure. This is the same as the Database parameter on an SQL connection component.

SCHEMA_NAME

ftString

The name of the schema that identifies the owner of the stored procedure.

PROC_NAME

ftString

The name of the stored procedure. This field determines the sort order of the dataset.

PROC_TYPE

ftInteger

Identifies the type of stored procedure. It is a sum of one or more of the following values: 1: Procedure 2: Function 4: Package 8: System procedure

IN_PARAMS

ftSmallint

The number of input parameters

OUT_PARAMS

ftSmallint

The number of output parameters.



Information about fields

When you request information about the fields in a specified table (stColumns), the resulting dataset includes a record for each field. It includes the following columns:

Columns in tables of metadata listing fields :

Column name Field type Contents

RECNO

ftInteger

A record number that uniquely identifies each record.

CATALOG_NAME

ftString

The name of the catalog (database) that contains the table whose fields you listing. This is the same as the Database parameter on an SQL connection component.

SCHEMA_NAME

ftString

The name of the schema that identifies the owner of the field.

TABLE_NAME

ftString

The name of the table that contains the fields.

COLUMN_NAME

ftString

The name of the field. This value determines the sort order of the dataset.

COLUMN_POSITION

ftSmallint

The position of the column in its table.

COLUMN_TYPE

ftInteger

Identifies the type of value in the field. It is a sum of one or more of the following: 1: Row ID 2: Row Version 4: Auto increment field 8: Field with a default value

COLUMN_DATATYPE

ftSmallint

The datatype of the column. This is one of the logical field type constants defined in sqllinks.pas.

COLUMN_TYPENAME

ftString

A string describing the datatype. This is the same information as contained in COLUMN_DATATYPE and COLUMN_SUBTYPE, but in a form used in some DDL statements.

COLUMN_SUBTYPE

ftSmallint

A subtype for the column's datatype. This is one of the logical subtype constants defined in sqllinks.pas.

COLUMN_PRECISION

ftInteger

The size of the field type (number of characters in a string, bytes in a bytes field, significant digits in a BCD value, members of an ADT field, and so on).

COLUMN_SCALE

ftSmallint

The number of digits to the right of the decimal on BCD values, or descendants on ADT and array fields.

COLUMN_LENGTH

ftInteger

The number of bytes required to store field values.

COLUMN_NULLABLE

ftSmallint

A Boolean that indicates whether the field can be left blank (0 means the field requires a value).



Information about indexes

When you request information about the indexes on a table (stIndexes), the resulting dataset includes a record for each field in each record. (Multi-record indexes are described using multiple records) The dataset has the following columns:

Columns in tables of metadata listing indexes :

Column name Field type Contents

RECNO

ftInteger

A record number that uniquely identifies each record.

CATALOG_NAME

ftString

The name of the catalog (database) that contains the index. This is the same as the Database parameter on an SQL connection component.

SCHEMA_NAME

ftString

The name of the schema that identifies the owner of the index.

TABLE_NAME

ftString

The name of the table for which the index is defined.

INDEX_NAME

ftString

The name of the index. This field determines the sort order of the dataset.

PKEY_NAME

ftString

Indicates the name of the primary key.

COLUMN_NAME

ftString

The name of the field (column) in the index.

COLUMN_POSITION

ftSmallint

The position of this field in the index.

INDEX_TYPE

ftSmallint

Identifies the type of index. It is a sum of one or more of the following values: 1: Non-unique 2: Unique 4: Primary key

SORT_ORDER

ftString

Indicates that the index is ascending (a) or descending (d).

FILTER

ftString

Describes a filter condition that limits the indexed records.



Information about stored procedure parameters

When you request information about the parameters of a stored procedure (stProcedureParams), the resulting dataset includes a record for each parameter. It has the following columns:

Columns in tables of metadata listing parameters :

Column name Field type Contents

RECNO

ftInteger

A record number that uniquely identifies each record.

CATALOG_NAME

ftString

The name of the catalog (database) that contains the stored procedure. This is the same as the Database parameter on an SQL connection component.

SCHEMA_NAME

ftString

The name of the schema that identifies the owner of the stored procedure.

PROC_NAME

ftString

The name of the stored procedure that contains the parameter.

PARAM_NAME

ftString

The name of the parameter. This field determines the sort order of the dataset.

PARAM_TYPE

ftSmallint

Identifies the type of parameter. This is the same as a TParam object's ParamType property.

PARAM_DATATYPE

ftSmallint

The datatype of the parameter. This is one of the logical field type constants defined in sqllinks.pas.

PARAM_SUBTYPE

ftSmallint

A subtype for the parameter's datatype. This is one of the logical subtype constants defined in sqllinks.pas.

PARAM_TYPENAME

ftString

A string describing the datatype. This is the same information as contained in PARAM_DATATYPE and PARAM_SUBTYPE, but in a form used in some DDL statements.

PARAM_PRECISION

ftInteger

The maximum number of digits in floating-point values or bytes (for strings and Bytes fields).

PARAM_SCALE

ftSmallint

The number of digits to the right of the decimal on floating-point values.

PARAM_LENGTH

ftInteger

The number of bytes required to store parameter values.

PARAM_NULLABLE

ftSmallint

A Boolean that indicates whether the parameter can be left blank (0 means the parameter requires a value).



Information about Oracle packages

Columns in tables of metadata listing stored procedures :

Column Name Field type Contents

RECNO

ftInteger

A record number that uniquely identifies each record.

CATALOG_NAME

ftString

The name of the catalog (database) that contains the package. This is the same as the Database parameter on an SQL connection component.

SCHEMA_NAME

ftString

The name of the schema that identifies the owner of the package.

OBJECT_NAME

ftString

The name of the package. This field determines the sort order of the dataset.



See Also