Friday, January 20, 2017

Informatica Metadata Queries - 4 SOURCE TABLES



4.1         List of source tables


SELECT
B.SUBJ_NAME,
C.DBDNAM,
D.DBTYPE_NAME,
A.SOURCE_NAME AS TABLE_NAME,
A.FILE_NAME SCHEMA_NAME,
A.OWNERNAME
FROM
OPB_SRC A,OPB_SUBJECT B, OPB_DBD C,OPB_MMD_DBTYPE D
WHERE A.SUBJ_ID = B.SUBJ_ID
AND A.DBDID = C.DBDID
AND C.DBTYPE = D.DBTYPE_ID
--AND A.SOURCE_NAME <> A.FILE_NAME
ORDER BY 1,2,3,4,5

4.2         List and count of tables in each folder by db type


SELECT
B.SUBJ_NAME,
D.DBTYPE_NAME,
count(*)
FROM
OPB_SRC A,OPB_SUBJECT B, OPB_DBD C,OPB_MMD_DBTYPE D
WHERE A.SUBJ_ID = B.SUBJ_ID
AND A.DBDID = C.DBDID
AND C.DBTYPE = D.DBTYPE_ID
--AND A.SOURCE_NAME <> A.FILE_NAME
group by B.SUBJ_NAME,D.DBTYPE_NAME
order by 1,2,3

4.3         List and count of tables overall used


SELECT SOURCE_NAME, COUNT(SOURCE_NAME) FROM REP_TBL_MAPPING
GROUP BY SOURCE_NAME
ORDER BY 1,2 ASC

4.4         List of source tables used in mapping


SELECT SUBJECT_AREA,SOURCE_NAME,MAPPING_NAME FROM REP_SRC_MAPPING
ORDER BY 1,2,3

4.5         List of source tables using as shortcuts


SELECT DISTINCT
B.SUBJ_NAME,
C.DBDNAM,
D.DBTYPE_NAME,
A.SOURCE_NAME AS TABLE_NAME,
A.FILE_NAME SCHEMA_NAME,
A.OWNERNAME
FROM
OPB_SRC A,OPB_SUBJECT B, OPB_DBD C,OPB_MMD_DBTYPE D
WHERE A.SUBJ_ID = B.SUBJ_ID
AND A.DBDID = C.DBDID
AND C.DBTYPE = D.DBTYPE_ID
--AND A.SOURCE_NAME <> A.FILE_NAME
and A.SOURCE_NAME like 'sc_%'
ORDER BY 1,2,3,4,5



4.6         List Source table where all its used in Mapping with Source Database Name


This view provides a list of the latest version of sources defined in each folder of a repository. Sources include both relational sources and non-relational sources such as XML files and flat files.

SELECT REP_ALL_SOURCES.SOURCE_NAME,
       REP_SRC_MAPPING.MAPPING_NAME,
       REP_ALL_SOURCES.SOURCE_DATABASE_NAME
  FROM REP_SRC_MAPPING, REP_ALL_SOURCES
 WHERE REP_SRC_MAPPING.SOURCE_ID(+) = REP_ALL_SOURCES.SOURCE_ID
       AND REP_SRC_MAPPING.SOURCE_NAME(+) =
              REP_ALL_SOURCES.PARENT_SOURCE_NAME
       AND REP_SRC_MAPPING.SUBJECT_ID(+) = REP_ALL_SOURCES.SUBJECT_ID
ORDER BY 1,2




4.7         List All Columns, DB, Length of a Source Table


SELECT
  SUBJECT_AREA,
  PARENT_SOURCE_DATABASE_NAME,
  PARENT_SOURCE_DATABASE_TYPE,
  PARENT_SOURCE_NAME as SOURCE_TABLE_NAME,
  SOURCE_FIELD_NUMBER,
  SOURCE_FIELD_NAME,
  SOURCE_FIELD_DATATYPE,
  SOURCE_FIELD_PHYSICAL_LENGTH,
  SOURCE_FIELD_KEY_TYPE,
  PARENT_SOURCE_LAST_SAVED
from REP_ALL_SOURCE_FLDS
where parent_source_name = 'SRC_TABLE_NAME'
and parent_subject_area = 'FOLDER_NAME'
ORDER BY SOURCE_FIELD_NUMBER

4.8         List all Flat files used as Source


This view provides a list of all file definitions in the repository. Use FIRST_FIELD_ID to retrieve the fields belonging to a non-relational source by following the links in the REP_SRC_FILE_FLDS view. Any flat file imported through the Source Analyzer has an entry.


SELECT *
FROM
REP_SRC_FILES
WHERE
subject_area = 'FOLDER_NAME'




4.9         List all Relational Tables used as Source


This view provides a list of relational database table sources that have been analyzed through the Source Analyzer tool or imported from a DDL (Data Definition Language) file.


SELECT *
FROM
REP_SRC_TBLS
WHERE
subject_area = 'FOLDER_NAME'
ORDER BY 1

4.10      Check where all a column is used in different tables and Folders.


SELECT *
FROM
REP_SRC_TBL_FLDS
WHERE
Column_Name = 'PH_DIMSN_KEY'
order by 1


4 comments:

  1. I’m excited to uncover this page. I need to to thank you for ones time for this particularly fantastic read !! I definitely really liked every part of it and i also have you saved to fav to look at new information in your site.
    data science course in guwahati

    ReplyDelete
  2. Really an awesome blog, Informative content. Keep sharing more stuff like this. Thank you.
    Data Science Institute in Hyderabad

    ReplyDelete