FileMaker system tables

Every FileMaker Pro database file includes these system tables:

  • FileMaker_Tables

  • FileMaker_Fields

  • FileMaker_BaseTables

  • FileMaker_BaseTableFields

For ODBC applications, these tables are included in the information returned by the catalog function SQLTables. For JDBC applications, these tables are included in the information returned by the DatabaseMetaData method getTables. The tables can also be used in ExecuteSQL functions.

FileMaker_Tables table

The FileMaker_Tables table contains information about the database tables defined in the FileMaker Pro file.

The FileMaker_Tables table includes a row for each table occurrence in the relationships graph with the following columns:

  • TableName - The name of the table occurrence.

  • TableId - The unique ID for the table occurrence.

  • BaseTableName - The name of the base table from which the table occurrence was created.

  • BaseFileName - The FileMaker Pro filename for the database file that contains the base table.

  • ModCount - The total number of times changes to this table’s definition have been committed.

Example

Copy 
SELECT TableName FROM FileMaker_Tables WHERE TableName LIKE 'Sales%'

FileMaker_Fields table

The FileMaker_Fields table contains information about the fields defined in the FileMaker Pro file for all table occurrences.

The FileMaker_Fields table includes the following columns:

  • TableName - The name of the table that contains the field.

  • FieldName - The name of the field.

  • FieldType - The SQL data type of the field.

  • FieldId - The unique ID for the field.

  • FieldClass - One of three values: Summary, for summary fields; Calculated, for calculated results; or Normal.

  • FieldReps - The number of repetitions of the field.

  • ModCount - The total number of times changes to this table’s definition have been committed.

Example

Copy 
SELECT * FROM FileMaker_Fields WHERE TableName='Sales'

FileMaker_BaseTables table

The FileMaker_BaseTables table contains information about only the source (or base) tables defined in the FileMaker Pro file. Because it doesn't include all table occurrences as FileMaker_Tables does, queries of the FileMaker_BaseTables table may be faster.

The FileMaker_BaseTables table includes a row for each base table with the following columns:

  • BaseTableName - The name of the base table.

  • BaseTableId - The unique ID for the base table.

  • Source - One of the following about the data source for the base table:

    • <Internal> - The table is in the current FileMaker Pro file.

    • <Missing> - The data source is not available.

    • The name of the external data source the base table is available from.

  • ModCount - The total number of times changes to this base table’s definition have been committed.

Example

Copy 
SELECT BaseTableName FROM FileMaker_BaseTables WHERE BaseTableName LIKE 'Sales%'

FileMaker_BaseTableFields table

The FileMaker_BaseTableFields table contains information about the fields defined in the FileMaker Pro file for only the source (or base) tables.

The FileMaker_BaseTableFields table includes the following columns:

  • BaseTableName - The name of the base table that contains the field.

  • FieldName - The name of the field.

  • FieldType - The SQL data type of the field.

  • FieldId - The unique ID for the field.

  • FieldClass - One of three values: Summary, for summary fields; Calculated, for calculated results; or Normal.

  • FieldReps - The number of repetitions of the field.

  • ModCount - The total number of times changes to this base table’s definition have been committed.

Example

Copy 
SELECT * FROM FileMaker_BaseTableFields WHERE BaseTableName='Sales'