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