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 &
