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 |