Friday, July 29, 2016

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 &

No comments:

Post a Comment