Query related to source tables in Informatica

 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

 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

 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

List of source tables used in mapping

SELECT SUBJECT_AREA,SOURCE_NAME,MAPPING_NAME FROM REP_SRC_MAPPING

ORDER BY 1,2,3

Informatica

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