Query all lookup table names through scripts

You must use SQL queries to retrieve this information.

The following script queries the names of all defined lookup tables from the product database tables and returns them in an array. You can use this code sample in the script sandbox. The defined function getAllLookupTableNames also can be part of a trigger script.

// ######################################################
///@brief getAllLookupTableNames() - returns the name of all lookup tables in a hash map
///@return hashmap "hmAllLookupTables" cotaining strings with names of existing lookup tables
// ######################################################
function getAllLookupTableNames() {
var strCmpName = getCompanyCode();
var strSQLquery = "select CTG_NAME from TCTG_CTG_CATALOG where CTG_TYPE = 'LOOKUP_TABLE' and CTG_COMPANY_ID = (select CMP_COMPANY_ID from TSEC_CMP_COMPANY where CMP_COMPANY_NAME = '" + strCmpName + "')";
var hmLookupTables = [];

var dbContext = getWPCDBContext();
var connection = dbContext.getWPCDBConnection();
var rsLookupTables = connection.executeQuery(strSQLquery);
connection.commit();
while(rsLookupTables.next()){
hmLookupTables.add(rsLookupTables.getColumnAt(1));
}
dbContext.releaseWPCDBConnection(connection);
return hmLookupTables;
}

// main script which calls function "getAllLookupTableNames()"
var i = 0;
var hmAllLookupTables = getAllLookupTableNames();
var iNumberOfLookupTables = hmAllLookupTables.size();

if( iNumberOfLookupTables == 0 ) {
out.writeln("No lookup tables have been defined in this company");
} else {
out.writeln(iNumberOfLookupTables + " lookup tables have been defined in this company:");
for( i = 0; i < iNumberOfLookupTables ; i++) {
out.writeln("Lookup table # " + i + ": \"" + hmAllLookupTables[i] + "\"");
}
}

The following sample output is displayed:

7 lookup tables have been defined in this company:
Lookup table # 0: LDAP Properties
Lookup table # 1: Group
Lookup table # 2: Currency
Lookup table # 3: BrandName
Lookup table # 4: ItemType
Lookup table # 5: Language
Lookup table # 6: PriceCode
Lookup table # 7: CustomerStatus