GetTableDDL

Returns table information in Data Definition Language (DDL) format for a list of table occurrences specified as a JSON array.

Format 

GetTableDDL ( tableOccurrenceNames ; ignoreError )

Parameters 

tableOccurrenceNames - a text expression for a JSON array of the table occurrences to return.

ignoreError - if set to True, returns DDL information for the table occurrences that cause no error. If False, returns DDL information when no table occurrences cause an error; otherwise returns "?" to indicate an error and logs the table occurrences that caused the error in the AI call log (see Set AI Call Logging script step).

Data type returned 

text

Originated in version 

21.0

Description 

This function returns a DDL representation of the specified table occurrences in the current file. The DDL consists of SQL commands that can be used to define database schema (tables, fields, and relationships).

Notes 

  • Along with table and field names, the returned DDL includes the field comments entered in the Manage Database dialog. This additional information about each field may help a model to provide more useful SQL queries.

Example 1 

GetTableDDL ( "[\"Meetings\", \"Topics\"]"; True) returns the following for a FileMaker Pro file containing table occurrences named Meetings and Topics:

Copy 
CREATE TABLE "Meetings" (
"Title" varchar(255),
"Location" varchar(255),
"Date" datetime,
"Start Time" datetime,
"End Time" datetime,
"Duration" varchar(255),
"Note" varchar(255),
"PrimaryKey" varchar(255), /*Unique identifier of each record in this table*/
"CreatedBy" varchar(255), /*Account name of the user who created each record*/
"ModifiedBy" varchar(255), /*Account name of the user who last modified each record*/
"CreationTimestamp" datetime, /*Date and time each record was created*/
"ModificationTimestamp" datetime, /*Date and time each record was last modified*/
"Note_Embedding" varbinary(4096),
PRIMARY KEY (PrimaryKey)
);

CREATE TABLE "Topics" (
"Subject" varchar(255),
"Text" varchar(255),
"ForeignKey" varchar(255), /*Unique identifier of each record in the related table*/
"Number of Topics" int,
"PrimaryKey" varchar(255), /*Unique identifier of each record in this table*/
"CreatedBy" varchar(255), /*Account name of the user who created each record*/
"ModifiedBy" varchar(255), /*Account name of the user who last modified each record*/
"CreationTimestamp" datetime, /*Date and time each record was created*/
"ModificationTimestamp" datetime, /*Date and time each record was last modified*/
PRIMARY KEY (PrimaryKey),
FOREIGN KEY (ForeignKey) REFERENCES Meetings(PrimaryKey)
);

Example 2 

Turns on AI call logging, then for an array of table occurrence names, checks whether GetTableDDL returns an error with the ignoreError parameter set to False.

Copy 
Set AI Call Logging [ On ; Filename: "ai-call.log" ; Verbose: Off ]
Set Variable [ $tableDDL ; Value: 
    GetTableDDL ( JSONMakeArray ( "Meetings,Employees,Contact Info" ; "," ; JSONString ) ; 
    False ) ]
If [ $tableDDL = "?" ]
    Show Custom Dialog [ "An error occurred. See ai-call.log in the Documents folder." ]
Else
    Show Custom Dialog [ $tableDDL ]
End If

Related topics