Search Informatica for text in SQL override

Question

Is there a way to search all the mappings, sessions, etc. in Informatica for a text string contained within a SQL override?

For example, suppose I know a certain stored procedure (SP_FOO) is being called somewhere in an INFA process, but I don't know where exactly. Somewhere I think there is a Post SQL on a source or target calling it. Could I search all the sessions for Post SQL containing SP_FOO ? (Similar to what I could do with grep with source code.)

Answer 1

If you have read access to the schema where the informatica repository resides, try this.

SELECT DISTINCT f.subj_name folder, e.mapping_name, object_type_name,
                b.instance_name, a.attr_value
           FROM opb_widget_attr a,
                opb_widget_inst b,
                opb_object_type c,
                opb_attr d,
                opb_mapping e,
                opb_subject f
          WHERE a.widget_id = b.widget_id
            AND b.widget_type = c.object_type_id
            AND (   object_type_name = 'Source Qualifier'
                 OR object_type_name LIKE '%Lookup%'
                )
            AND a.widget_id = b.widget_id
            AND a.attr_id = d.attr_id
            AND c.object_type_id = d.object_type_id
            AND attr_name IN ('Sql Query')--, 'Lookup Sql Override')
            AND b.mapping_id = e.mapping_id
            AND e.subject_id = f.subj_id
            AND a.attr_value is not null
            --AND UPPER (a.attr_value) LIKE UPPER ('%currency%')