1.07 SOURCE TABLES

7.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

7.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

7.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

7.4 List of source tables used in mapping

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

7.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

7.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

7.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

7.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'

7.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

7.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