KNOWLEDGE BASE
Log In    |    Knowledge Base    |    4D Home
Tech Tip: Finding all tables that have a Primary Key defined
PRODUCT: 4D | VERSION: 12.6 | PLATFORM: Mac & Win
Published On: June 19, 2014
Your current browser may not allow you to download the asset. We recommend Mozilla Firefox or Google Chrome.
Log In

The following code snippet can be used to get an array of all table names that have a Primary Key defined:

ARRAY TEXT(pk_Tables;0)
Begin SQL
   SELECT TABLE_NAME
   FROM _USER_CONSTRAINTS
   WHERE CONSTRAINT_TYPE = 'P'
   INTO :pk_Tables;

End SQL


This sample code can be useful if you want to quickly know which tables have a primary key defined.



The following example is a bit more robust in that a For loop is constructed where both the table name and a pointer to the table is available:

ARRAY TEXT(pk_TableNames;0)
ARRAY LONGINT(pk_TableIDs;0)
Begin SQL
    SELECT TABLE_NAME, TABLE_ID
    FROM _USER_CONSTRAINTS
    WHERE CONSTRAINT_TYPE = 'P'
    INTO :pk_TableNames, :pk_TableIDs;

End SQL


For ($a;1;Size of array(pk_TableNames))
    // loop over each table with a PK

   $table_ptr:=Table(pk_TableIDs{$a})
   $table_name:=pk_TableNames{$a}

    // do stuff

End for