728x90

1. 테이블 정의서 추출 쿼리

SELECT
    A.OWNER AS "스키마"
    A.TABLE_NAME AS "테이블 영문명"
    A.COMMENTS AS "테이블 한글명"
FROM
    ALL_TBL_COMMENTS A
    LEFT JOIN ALL_TAB_COLUMNS B ON A.OWNER = B.OWNER AND A.TABLE_NAME = B.TABLE_NAME
WHERE
    A.OWNER = '[스키마명]'
    AND A.TABLE_TYPE != 'VIEW'
GROUP BY A.OWNER, A.TABLE_NAME, A.COMMENTS

 

 

2. 컬럼 정의서 추출 쿼리

SELECT
    A.OWNER AS "스키마"
    A.TABLE_NAME AS "테이블 영문명",
    A.COLUMN_NAME AS "컬럼 영문명",
    B.COMMENTS AS "컬럼 한글명",
    B.COMMENTS AS "컬럼 설명",
    A.DATA_TYPE AS "데이터타입",
    A.DATA_LENGTH AS "전체길이",
    A.DATA_SCALE AS "소수점길이",
    A.NULLBLE AS "NULL허용여부"
FROM
	ALL_TABL_COLUMNS A
    INNER JOIN ALL_COL_COMMENTS B ON A.OWNER = B.OWNER 
    AND A.TABLE_NAME = B.TABLE_NAME 
    AND A.COLUMN_NAME AND B.COLUMN_NAME
WHERE A.OWNER = '[스키마명]'
728x90
TOP