Netezza: find object dependencies

SQL query to search for object name referenced in current_catalog database views and procedures source code definitions.  

---

---- Find object name (e.g. string) in views and stored procedures ---

select  (select DECODE (VAL,'sdw1','NZ1','NZ2') from _t_environ where NAME = 'HOSTNAME')|| ': HOST' AS HOST

        , '*'||p.str||'*'    AS SEARCH_STRING

        ,s.objtype

        , current_catalog as database

        ,s.objname

        ,s.sourcecode

        ,s.createdate as objcreated

from     (select 'Proc' as objtype,proceduresignature as objname, proceduresource as sourcecode, createdate from _v_procedure

union all select 'View' as objtype,viewname           as objname, definition      as sourcecode, createdate from _v_view) as s

CROSS JOIN (SELECT  'TABLE2SEARCH4_1'             as str

  union all SELECT 'VIEW2SEARCH4_1'

  union all SELECT 'TABLE2SEARCH4_2'

  union all SELECT 'VIEW2SEARCH4_2' ) p

where SQLX..REGEXP_LIKE(s.sourcecode,p.str,'i')

;

---

Extract database VIEWS referencing other views and tables.  

--- extract VIEWS definitions into a flat file, then parse file searching for table name

nz_view_references DBNAME > ~/$(date +'%Y%m%d%H%M%S')_$(hostname)_nz_view_references_DBNAME.txt &

egrep -ri "STR2SEARCH"  $(date +'%Y%m%d')*_$(hostname _sdw1_nz_view_references_*.txt

---

Extract entire appliance [database] DDL for all objects including views and procedures:

/nz/ support/bin/nz_ddl [DBNAME] > ~/$(date +'%Y%m%d%H%M%S' )_$(hostname)_nz_ddl.sql &