How can I find all fields in all tables in a MUF where the field name contains a specific character pattern?
Field names that contain both 'NUM' and 'SEQ', or contain both 'DAT' and 'SEQ'?
Release : 15.1
Component : CA DATACOM/DB
The following SQL will list all fields in PROD status by DBID and TABLE that contain the specific character pattern:
SELECT A.DBID, A.OCCURRENCE AS "TABLE",
B.ENTITY_NAME AS "FIELD", B.STATUS, B.LENGTH
FROM SYSADM.DIR_TABLE A, SYSADM.FIELD B
WHERE A.OCCURRENCE = B.RECORD_NAME
AND B.STATUS = 'P'
AND ((ENTITY_NAME LIKE '%NUM%' AND ENTITY_NAME LIKE '%SEQ%' )
OR (ENTITY_NAME LIKE '%DAT%' AND ENTITY_NAME LIKE '%SEQ%' )}
ORDER BY A.DBID, A.OCCURRENCE;
Change the higlighted characters NUM, DAT and SEQ to the specific characters you want.
Also see article 15152 - How to identify the Datacom TABLEs with a specific FIELD?