Is there a way to check the ‘Connection path’ between two tables using oracle?

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.

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'