전체 테이블, 칼럼 내용 출력 (PK, FK 여부 포함)

--테이블(논리)명 테이블(물리)명 컬럼(논리)명 컬럼(물리)명 TYPE&LENGTH NULL PK FK

SELECT B.COMMENTS, T.TABLE_NAME, C.COMMENTS, T.COLUMN_NAME, T.DATA_TYPE

|| '('|| CASE WHEN T.DATA_PRECISION IS NULL THEN T.DATA_LENGTH ELSE T.DATA_PRECISION END ||')'

    , CASE WHEN T.NULLABLE = 'Y' THEN 'NULL' ELSE 'NOT NULL' END

    , CASE WHEN E.COLUMN_NAME IS NOT NULL THEN 'PK' END PK

    , CASE WHEN F.COLUMN_NAME IS NOT NULL THEN 'FK' END FK

FROM USER_TAB_COLUMNS T

LEFT OUTER JOIN USER_TAB_COMMENTS B

  ON B.TABLE_NAME = T.TABLE_NAME

LEFT OUTER JOIN USER_COL_COMMENTS C

  ON C.TABLE_NAME = T.TABLE_NAME

    AND C.COLUMN_NAME = T.COLUMN_NAME

  LEFT OUTER JOIN

  (SELECT B.TABLE_NAME, B.COLUMN_NAME

    FROM USER_CONSTRAINTS A

      INNER JOIN USER_CONS_COLUMNS B

        ON B.TABLE_NAME = A.TABLE_NAME

    WHERE CONSTRAINT_TYPE = 'P'

    GROUP BY B.TABLE_NAME, B.COLUMN_NAME

    ) E ON E.TABLE_NAME = T.TABLE_NAME AND E.COLUMN_NAME = T.COLUMN_NAME

   LEFT OUTER JOIN

  (SELECT B.TABLE_NAME, B.COLUMN_NAME

    FROM USER_CONSTRAINTS A

      INNER JOIN USER_CONS_COLUMNS B

        ON B.TABLE_NAME = A.TABLE_NAME

    WHERE CONSTRAINT_TYPE = 'F'

    GROUP BY B.TABLE_NAME, B.COLUMN_NAME

    ) F ON F.TABLE_NAME = T.TABLE_NAME AND F.COLUMN_NAME = T.COLUMN_NAME

ORDER BY T.TABLE_NAME, T.COLUMN_ID

;