For two given tables ‘foo’ and ‘bar’ is there a way to write a SQL Query that return the path connection between those?
[ foo ] —> [ n’tables ] —> [ bar ]
I also want to check if there is a way to know if there is a valid connection path that connects two different tables in a database without using ERD.
Advertisement
Answer
WITH FIRST_LEVEL_PK_FK_TITLE AS (
SELECT PK.TABLE_NAME TABLE_NAME_1
FROM USER_CONSTRAINTS FK
JOIN USER_CONSTRAINTS PK
ON PK.CONSTRAINT_NAME = FK.R_CONSTRAINT_NAME AND PK.OWNER = FK.OWNER
WHERE PK.TABLE_NAME in ( 'TABLE_NAME' ) OR FK.TABLE_NAME in ( 'TABLE_NAME')
UNION
SELECT FK.TABLE_NAME TABLE_NAME_1
FROM USER_CONSTRAINTS FK
JOIN USER_CONSTRAINTS PK
ON PK.CONSTRAINT_NAME = FK.R_CONSTRAINT_NAME AND PK.OWNER = FK.OWNER
WHERE PK.TABLE_NAME in ( 'TABLE_NAME') OR FK.TABLE_NAME in ( 'TABLE_NAME')
),
PUR AS (
SELECT CONSTRAINT_NAME,
CONSTRAINT_TYPE,
TABLE_NAME,
R_CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE IN ('P', 'U', 'R')
),
RELATIONS AS (
SELECT A.TABLE_NAME TABLE_NAME_REL,
B.TABLE_NAME R_TABLE_NAME,
B.CONSTRAINT_TYPE R_CONSTRAINT_TYPE
FROM PUR A JOIN PUR B ON (A.R_CONSTRAINT_NAME) = ((B.CONSTRAINT_NAME))
)
, WITH_PARENTS AS (
SELECT * FROM RELATIONS
UNION
SELECT R_TABLE_NAME, NULL, NULL
FROM RELATIONS
WHERE (R_TABLE_NAME) NOT IN (SELECT TABLE_NAME_REL
FROM RELATIONS
WHERE (TABLE_NAME_REL) != ((R_TABLE_NAME)))
),
WITH_ROOT_LEVEL AS (
SELECT DISTINCT ROOT, TABLE_NAME_REL, R_TABLE_NAME
FROM
(SELECT CONNECT_BY_ROOT(TABLE_NAME_REL) AS ROOT, TABLE_NAME_REL, R_TABLE_NAME
FROM WITH_PARENTS
START WITH R_TABLE_NAME IS NULL
CONNECT BY NOCYCLE ( R_TABLE_NAME) = ( PRIOR TABLE_NAME_REL)
)
WHERE R_TABLE_NAME IN (SELECT TABLE_NAME_1 FROM FIRST_LEVEL_PK_FK_TITLE)
OR TABLE_NAME_REL IN (SELECT TABLE_NAME_1 FROM FIRST_LEVEL_PK_FK_TITLE)
),
SECOND_LEVEL_PK_FK_TITLE AS (
SELECT DISTINCT ROOT TABLE_NAME_2 FROM WITH_ROOT_LEVEL
UNION
SELECT DISTINCT TABLE_NAME_REL TABLE_NAME_2 FROM WITH_ROOT_LEVEL
UNION
SELECT DISTINCT R_TABLE_NAME TABLE_NAME_2 FROM WITH_ROOT_LEVEL
UNION
SELECT DISTINCT TABLE_NAME_1 TABLE_NAME_2 FROM FIRST_LEVEL_PK_FK_TITLE
),
WITH_ROOT_LEVEL_2 AS (
SELECT DISTINCT ROOT_2,TABLE_NAME_REL_2, R_TABLE_NAME_2
FROM
(SELECT CONNECT_BY_ROOT(TABLE_NAME_REL) AS ROOT_2, TABLE_NAME_REL TABLE_NAME_REL_2,R_TABLE_NAME R_TABLE_NAME_2
FROM WITH_PARENTS
START WITH R_TABLE_NAME IS NULL
CONNECT BY NOCYCLE ( R_TABLE_NAME) = ( PRIOR TABLE_NAME_REL)
)
WHERE R_TABLE_NAME_2 IN (SELECT TABLE_NAME_2 FROM SECOND_LEVEL_PK_FK_TITLE)
OR TABLE_NAME_REL_2 IN (SELECT TABLE_NAME_2 FROM SECOND_LEVEL_PK_FK_TITLE)
),
THIRD_LEVEL_PK_FK_TITLE AS (
SELECT ROW_NUMBER() OVER ( ORDER BY TABLE_NAME_ALL ) TABLE_RN, TABLE_NAME_ALL
FROM (
SELECT DISTINCT ROOT_2 TABLE_NAME_ALL FROM WITH_ROOT_LEVEL_2
UNION
SELECT DISTINCT TABLE_NAME_REL_2 TABLE_NAME_ALL FROM WITH_ROOT_LEVEL_2
UNION
SELECT DISTINCT R_TABLE_NAME_2 TABLE_NAME_ALL FROM WITH_ROOT_LEVEL_2
UNION
SELECT DISTINCT TABLE_NAME_2 TABLE_NAME_ALL FROM SECOND_LEVEL_PK_FK_TITLE
)
-- WHERE TABLE_NAME_ALL IS NOT NULL AND TABLE_NAME_ALL NOT IN ('EOR_CRUISE')
),
COLS_COMMENT as (
SELECT THIRD_LEVEL_PK_FK_TITLE.TABLE_RN,
UTC.TABLE_NAME TABLE_NAME,
UTC.COLUMN_NAME COLUMN_NAME,
CASE WHEN UTC.DATA_TYPE in ('VARCHAR2','CHAR', 'CLOB','NCLOB','LONG', 'NVARCHAR2','BLOB' )
THEN 'string'
WHEN UTC.DATA_TYPE IN ('DATE','TIMESTAMP(6)','TIMESTAMP(3)')
THEN 'date'
WHEN UTC.DATA_TYPE = 'NUMBER' AND UTC.DATA_PRECISION = 28 AND UTC.DATA_SCALE = 0
THEN 'integer'
WHEN UTC.DATA_TYPE = 'NUMBER' AND UTC.DATA_SCALE = 0
THEN 'number'
WHEN UTC.DATA_TYPE = 'NUMBER' AND (UTC.DATA_SCALE > 0 OR UTC.DATA_SCALE IS NULL)
THEN 'number'
END COLUMN_TYPE,
UTC.NULLABLE NULLABLE,
COM.COMMENTS TAB_COMMENTS,
CCOM.COMMENTS COL_COMMENTS
FROM USER_TAB_COLUMNS UTC
INNER JOIN USER_TAB_COMMENTS COM ON COM.TABLE_NAME = UTC.TABLE_NAME
INNER JOIN USER_COL_COMMENTS CCOM ON CCOM.TABLE_NAME = UTC.TABLE_NAME
INNER JOIN THIRD_LEVEL_PK_FK_TITLE ON UTC.TABLE_NAME = THIRD_LEVEL_PK_FK_TITLE.TABLE_NAME_ALL
AND CCOM.COLUMN_NAME = UTC.COLUMN_NAME
ORDER BY TABLE_NAME, COLUMN_ID
),
REF_TABLE_NUMBER AS
(SELECT PK.TABLE_NAME PK_TABLE_NAME, UCC.COLUMN_NAME REF_COLUMN_NAME, FK.TABLE_NAME FK_TABLE_NAME, THIRD_LEVEL_PK_FK_TITLE.TABLE_RN REF_TABLE_RN
FROM USER_CONSTRAINTS FK
JOIN USER_CONSTRAINTS PK ON PK.CONSTRAINT_NAME = FK.R_CONSTRAINT_NAME AND PK.OWNER = FK.OWNER
JOIN USER_CONS_COLUMNS UCC ON UCC.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
JOIN THIRD_LEVEL_PK_FK_TITLE ON THIRD_LEVEL_PK_FK_TITLE.TABLE_NAME_ALL = PK.TABLE_NAME
WHERE FK.CONSTRAINT_TYPE = 'R'
),
TABLE_NAME_REFERENCE AS (
SELECT FK.TABLE_NAME FK_TABLE_NAME_REF, 'reference' TABLE_TYPE
FROM USER_CONSTRAINTS FK
JOIN USER_CONSTRAINTS PK ON PK.CONSTRAINT_NAME = FK.R_CONSTRAINT_NAME AND PK.OWNER = FK.OWNER
AND FK.CONSTRAINT_TYPE='R'
AND FK.TABLE_NAME <> PK.TABLE_NAME
GROUP BY FK.TABLE_NAME
)
,
THIRD_LEVEL_PK_FK_TITLE_MASTER AS (
SELECT ROW_NUMBER() OVER ( ORDER BY TABLE_NAME_ALL ) TABLE_RN, TABLE_NAME_ALL
FROM (
SELECT DISTINCT ROOT_2 TABLE_NAME_ALL FROM WITH_ROOT_LEVEL_2
UNION
SELECT DISTINCT TABLE_NAME_REL_2 TABLE_NAME_ALL FROM WITH_ROOT_LEVEL_2
UNION
SELECT DISTINCT R_TABLE_NAME_2 TABLE_NAME_ALL FROM WITH_ROOT_LEVEL_2
UNION
SELECT DISTINCT TABLE_NAME_2 TABLE_NAME_ALL FROM SECOND_LEVEL_PK_FK_TITLE
)
WHERE TABLE_NAME_ALL IS NOT NULL AND TABLE_NAME_ALL NOT IN ('EOR_CRUISE')
)
SELECT TABLE_RN TABLE_ID,
TABLE_NAME,
TAB_COMMENTS TABLE_COMMENT,
CASE WHEN TABLE_NAME_REFERENCE.TABLE_TYPE IS NULL
THEN 'master'
ELSE TABLE_NAME_REFERENCE.TABLE_TYPE
END TABLE_TYPE,
COLUMN_NAME,
COL_COMMENTS COLUMN_COMMENT,
CASE WHEN REF_TAB.REF_TABLE_RN IS NOT NULL
THEN 'ref'
ELSE COLUMN_TYPE
END COLUMN_TYPE,
REF_TAB.REF_TABLE_RN COLUMN_REF,
NULLABLE COLUMN_NULLABLE
FROM COLS_COMMENT
LEFT JOIN REF_TABLE_NUMBER REF_TAB ON REF_TAB.FK_TABLE_NAME=TABLE_NAME
AND REF_TAB.REF_COLUMN_NAME = COLUMN_NAME
LEFT JOIN TABLE_NAME_REFERENCE ON TABLE_NAME_REFERENCE.FK_TABLE_NAME_REF=TABLE_NAME
WHERE NVL(COL_COMMENTS, 'NOT_USED') <>'NOT_USED'