Schema Discovery
You can use the FireDAC APIs to perform schema discovery or you can execute SQL to the available System Tables.
Using FireDAC Schema Discovery Classes
The TFDConnection and TFDMetaInfoQuery classes provide methods for surfacing metadata, including schema information. TFDConnection returns a string list while TFDMetaInfoQuery returns a dataset.List Tables and Views
The following call outputs a strings list of all of the tables and views in the database:
FDConnection1.GetTableNames('', '', '', ListBox1.Items);Below is the corresponding TFDMetaInfoQuery call:
FDMetaInfoQuery1.Connection := FDConnection1; FDMetaInfoQuery1.MetaInfoKind := mkTables; FDMetaInfoQuery1.Open;The mkTables MetaInfoKind value returns the following columns:
- RECNO: dtInt32
- CATALOG_NAME: dtWideString
- SCHEMA_NAME: dtWideString
- TABLE_NAME: dtWideString
- TABLE_TYPE: dtInt32
List Table Columns
The following call outputs all the columns for the specified table:
FDConnection1.GetFieldNames('', '', 'Person', '', ListBox1.Items);
Below is the corresponding TFDMetaInfoQuery call. When the MetaInfoKind property is mkTableFields, the ObjectName property must be set to the table name.
FDMetaInfoQuery1.Connection := FDConnection1; FDMetaInfoQuery1.MetaInfoKind := mkTableFields; FDMetaInfoQuery1.ObjectName := 'Account'; FDMetaInfoQuery1.Open;The mkTableFields MetaInfoKind value returns the following columns:
- RECNO: dtInt32
- CATALOG_NAME: dtWideString
- SCHEMA_NAME: dtWideString
- TABLE_NAME: dtWideString
- COLUMN_NAME: dtWideString
- COLUMN_POSITION: dtInt32
- COLUMN_DATATYPE: dtInt32
- COLUMN_TYPENAME: dtWideString
- COLUMN_ATTRIBUTES: dtUInt32
- COLUMN_PRECISION: dtInt32
- COLUMN_SCALE: dtInt32
- COLUMN_LENGTH: dtInt32
List Table Primary Keys
The following call outputs the primary keys for the specified table:
FDConnection1.GetKeyFieldNames('', '', 'Person', '', lbxKeyFields.Items);Below is the corresponding TFDMetaInfoQuery call. When the MetaInfoKind property is mkIndexes, the ObjectName property must be set to the table name.
FDMetaInfoQuery1.Connection := FDConnection1; FDMetaInfoQuery1.MetaInfoKind := mkIndexes; FDMetaInfoQuery1.ObjectName := 'Person'; FDMetaInfoQuery1.Open;The mkIndexes MetaInfoKind value returns the following columns:
- RECNO: dtInt32
- CATALOG_NAME: dtWideString
- SCHEMA_NAME: dtWideString
- TABLE_NAME: dtWideString
- INDEX_NAME: dtWideString
- CONSTRAINT_NAME: dtWideString
- INDEX_TYPE: dtInt32