CA Datacom report on field names that contain specific character pattern
search cancel

CA Datacom report on field names that contain specific character pattern

book

Article ID: 194158

calendar_today

Updated On:

Products

Datacom DATACOM - AD Datacom/DB INFOCAI MAINTENANCE

Issue/Introduction

How can I find all fields in all tables in a MUF where the field name contains a specific character pattern?

For example:

Field names that contain both 'NUM' and 'SEQ', or contain both 'DAT' and 'SEQ'? 

 

Environment

Release : 15.1

Component : CA DATACOM/DB

Resolution

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.

Additional Information

 Also see article 15152 - How to identify the Datacom TABLEs with a specific FIELD?