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