Friday, January 20, 2017

Informatica Metadata Queries - 3 INFORMATICA TABLES




3.1         List where a table is used


Sometimes you want to know if certain tables are listed in sql overrides of Source Qualifier or Lookup transformation. This helps you identifying dependencies. The query below will list folder, attribute type and sql override as output.

SELECT DISTINCT REP_ALL_MAPPINGS.SUBJECT_AREA, REP_ALL_MAPPINGS.MAPPING_NAME,REP_WIDGET_ATTR.ATTR_NAME, REP_WIDGET_ATTR.ATTR_VALUE
FROM REP_WIDGET_ATTR, REP_WIDGET_INST, REP_ALL_MAPPINGS
WHERE REP_WIDGET_ATTR.WIDGET_ID = REP_WIDGET_INST.WIDGET_ID
AND REP_WIDGET_INST.MAPPING_ID = REP_ALL_MAPPINGS.MAPPING_ID
AND REP_WIDGET_ATTR.WIDGET_TYPE IN (3,11)
AND REP_WIDGET_ATTR.ATTR_ID = 1
AND REP_WIDGET_ATTR.ATTR_VALUE LIKE '%' || REPLACE('TABLE_NAME', '_', '/_') || '%' ESCAPE '/'
ORDER BY 1,2,3


3.2         List all source and target tables of mapping 


SELECT DISTINCT SUBJECT_AREA,SOURCE_NAME,TARGET_NAME,MAPPING_NAME FROM REP_TBL_MAPPING
ORDER BY 1,2,3,4


3.3         List comments of all objects


SELECT
B.SUBJECT_AREA AS FOLDER_NAME, A.OBJECT_NAME,A.COMMENTS,  A.VERSION_NUMBER
FROM
REP_VERSION_PROPS A, REP_SUBJECT B
WHERE B.SUBJECT_ID = A.SUBJECT_ID
AND A.COMMENTS IS NOT NULL
ORDER BY 1,2

3.4         List of Database used in Repository


The database definition view provides a list of all database definitions in the repository. A database definition includes the source database names, flat file or RDBMS, and the folder where the database definition resides.


select subject_area,database_name,def_source from REP_DATABASE_DEFS

order by 1,2,3

5 comments:

  1. keep up the good work. this is an Assam post. this to helpful, i have reading here all post. i am impressed. thank you. this is our digital marketing training center. This is an online certificate course
    digital marketing training in bangalore | https://www.excelr.com/digital-marketing-training-in-bangalore

    ReplyDelete
  2. Good post. I learn something new and challenging on sites I stumbleupon on a daily basis. It's always interesting to read content from other writers and practice a little something from their web sites.
    Digital Marketing Training Course in Chennai | Digital Marketing Training Course in Anna Nagar | Digital Marketing Training Course in OMR | Digital Marketing Training Course in Porur | Digital Marketing Training Course in Tambaram | Digital Marketing Training Course in Velachery

    ReplyDelete