Netezza: search for string in all database tables. Parse all text fields.

PROBLEM: 

Search every field in the database searching for a string pattern.

SOLUTION: 

This will generate long SQL statement what will include all existing text columns for all tables within confines of the CURRENT_CATALOG database. 

select 'union all select '''||CURRENT_CATALOG||''' DB,'''||NAME||''' TAB,'''||ATTNAME||''' COL'

||',sum(sign(nvl(instr('||ATTNAME||',''My-String#1-Here''),0))) CNTROWS_STR1 '

||',sum(sign(nvl(instr('||ATTNAME||',''My-String#2-Here''),0))) CNTROWS_STR2 '

||',sum(sign(nvl(instr('||ATTNAME||',''My-String#N-Here''),0))) CNTROWS_STRN '

||' from '||CURRENT_CATALOG||'..'||NAME

from _v_relation_column

where OBJCLASS = 4905 --- TABLE

  and ATTTYPID in (

                 18   --- CHAR

                ,19   --- NAME

                ,25   --- TEXT

               ,1042  --- CHARACTER

               ,1043  --- CHARACTER VARYING

               ,2530  --- NATIONAL CHARACTER VARYING

               ,2522  --- NATIONAL CHARACTER

) ORDER BY 1

;

EXAMPLE: 

 SELECT * FROM (

          select 'NZDBA' DB,'NZ_DB_SIZE_HISTORY' TAB,'DATABASE' COL

,sum(sign(nvl(instr(DATABASE,'A'),0))) CNTROWS_STR1

,sum(sign(nvl(instr(DATABASE,'B'),0))) CNTROWS_STR2

,sum(sign(nvl(instr(DATABASE,'C'),0))) CNTROWS_STRN

 from NZDBA..NZ_DB_SIZE_HISTORY

union all select 'NZDBA' DB,'NZ_DB_SIZE_HISTORY' TAB,'NAME' COL

,sum(sign(nvl(instr(NAME,'A'),0))) CNTROWS_STR1

,sum(sign(nvl(instr(NAME,'B'),0))) CNTROWS_STR2

,sum(sign(nvl(instr(NAME,'C'),0))) CNTROWS_STRN

 from NZDBA..NZ_DB_SIZE_HISTORY

union all select 'NZDBA' DB,'NZ_DB_SIZE_HISTORY' TAB,'OBJCLASS' COL

,sum(sign(nvl(instr(OBJCLASS,'A'),0))) CNTROWS_STR1

,sum(sign(nvl(instr(OBJCLASS,'B'),0))) CNTROWS_STR2

,sum(sign(nvl(instr(OBJCLASS,'C'),0))) CNTROWS_STRN

 from NZDBA..NZ_DB_SIZE_HISTORY

union all select 'NZDBA' DB,'NZ_DB_SIZE_HISTORY' TAB,'SCHEMA' COL

,sum(sign(nvl(instr(SCHEMA,'A'),0))) CNTROWS_STR1

,sum(sign(nvl(instr(SCHEMA,'B'),0))) CNTROWS_STR2

,sum(sign(nvl(instr(SCHEMA,'C'),0))) CNTROWS_STRN

 from NZDBA..NZ_DB_SIZE_HISTORY

union all select 'NZDBA' DB,'NZ_GRA_HISTORY' TAB,'GROUPNAME' COL

,sum(sign(nvl(instr(GROUPNAME,'A'),0))) CNTROWS_STR1

,sum(sign(nvl(instr(GROUPNAME,'B'),0))) CNTROWS_STR2

,sum(sign(nvl(instr(GROUPNAME,'C'),0))) CNTROWS_STRN

 from NZDBA..NZ_GRA_HISTORY

 --- ... many more here ... ---

) as x

WHERE (CNTROWS_STR1+CNTROWS_STR2+CNTROWS_STR2) > 0  --- Filter added

ORDER BY 1,2,3                                      --- to discard no matched

;