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

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