Informatica 9.6.1 : MX Views query for list of tables

• Query on MX views for list of all tables used in the ETL code

SELECT TABLE_NAME, TABLE_TYPE FROM
(
---- Source Tables ---
SELECT DISTINCT 'Source Table' as TABLE_TYPE, PARENT_SOURCE_NAME TABLE_NAME FROM REP_ALL_SOURCES
UNION

---- Target Tables ---
SELECT DISTINCT 'Target Table' as TABLE_TYPE, PARENT_TARGET_NAME TABLE_NAME FROM REP_ALL_TARGETS
UNION
---- Lookup Tables ---
SELECT DISTINCT 'Lookup Table' as TABLE_TYPE, A.ATTR_VALUE TABLE_NAME FROM REP_ALL_TRANSFORMS T, REP_WIDGET_ATTR A
WHERE A.ATTR_DESCRIPTION = 'Lookup source table' AND A.WIDGET_ID = T.WIDGET_ID
AND T.WIDGET_TYPE_NAME = 'Lookup Procedure'
) order by 1;