테이블정의서 추출쿼리

테이블 정의서 추출쿼리

-- 역공학 QUERY => 테이블 정의서
WITH V_PRIMARY_KEY AS (
SELECT A.TABLE_NAME
, A.CONSTRAINT_TYPE
, A.CONSTRAINT_NAME
, B.COLUMN_NAME
, B.POSITION
FROM ALL_CONSTRAINTS A
, ALL_CONS_COLUMNS B
WHERE A.OWNER = 'LSMS'
AND A.CONSTRAINT_TYPE = 'P'
AND A.OWNER = B.OWNER
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
ORDER BY A.TABLE_NAME, A.CONSTRAINT_TYPE, A.CONSTRAINT_NAME, B.COLUMN_NAME, B.POSITION
)
SELECT NULL AS "엔티티명"
, A.TABLE_NAME AS "테이블명"
, NULL AS "속성명"
, A.COLUMN_NAME AS "컬럼명"
, B.COMMENTS AS "설명"
-- , A.DATA_TYPE
-- , A.DATA_LENGTH
, CASE A.DATA_TYPE WHEN 'VARCHAR2' THEN A.DATA_TYPE || '(' || A.DATA_LENGTH || ')'
WHEN 'CHAR' THEN A.DATA_TYPE || '(' || A.DATA_LENGTH || ')'
ELSE A.DATA_TYPE END AS DATATYPE
, CASE WHEN P.CONSTRAINT_TYPE IS NOT NULL THEN 'PK' ELSE '' END AS "PK"
, NULL AS "FK"
, CASE A.NULLABLE WHEN 'N' THEN 'NOT NULL' ELSE '' END AS "필수여부"
, A.DATA_DEFAULT AS "DEFAULT"
-- , A.DEFAULT_LENGTH
, NULL AS "도메인"
FROM ALL_TAB_COLUMNS A
LEFT JOIN V_PRIMARY_KEY P ON A.TABLE_NAME=P.TABLE_NAME AND A.COLUMN_NAME=P.COLUMN_NAME
LEFT JOIN ALL_COL_COMMENTS B ON A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME
WHERE A.OWNER ='소유자 계정'
AND B.COMMENTS IS NOT NULL
ORDER BY A.TABLE_NAME, A.COLUMN_ID;





// 특정 테이블에서 컬럼명 조회

select * from col where tname = '테이블명'



// 테이블 정의서 출력 Query

SELECT TBL.TABLE_NAME , TCM.COMMENTS , TBL.TABLESPACE_NAME , TCL.COLUMN_ID

, TCL.COLUMN_NAME

, CCM.COMMENTS

, DATA_TYPE

, DATA_LENGTH

/*

CASE WHEN TCL.DATA_TYPE = 'VARCHAR2' OR TCL.DATA_TYPE = 'CHAR' OR TCL.DATA_TYPE = 'NUMBER'

THEN TCL.DATA_TYPE || '(' || DATA_LENGTH || ')'

ELSE TCL.DATA_TYPE END AS DATA_TYPE

*/

,DECODE(NULLABLE , 'N' , 'N' , 'Y') AS NOTNULL

--, DECODE(CON.KEY, 'PK','P', 'FK','F','') PK

, DECODE(CON.KEY, 'PK','P', '') PK

, DATA_DEFAULT

FROM USER_TABLES TBL , USER_TAB_COMMENTS TCM , USER_TAB_COLUMNS TCL , USER_COL_COMMENTS CCM ,

(SELECT CCL.TABLE_NAME , COLUMN_NAME ,

CASE WHEN SUM(DECODE(CONSTRAINT_TYPE , 'P' , 1 , 0))>0 AND SUM(DECODE(CONSTRAINT_TYPE , 'F' , 1 , 0))>0

THEN 'PK,FK'

WHEN SUM(DECODE(CONSTRAINT_TYPE , 'P' , 1 , 0))>0

THEN 'PK'

WHEN SUM(DECODE(CONSTRAINT_TYPE , 'F' , 1 , 0))>0

THEN 'FK'

ELSE '' END AS KEY ,

SUM(DECODE(CONSTRAINT_TYPE , 'C' , 0 , 'P' , 0 , 'F' , 0 , 1)) AS CCC

FROM USER_CONS_COLUMNS CCL , USER_CONSTRAINTS CNS

WHERE CCL.CONSTRAINT_NAME = CNS.CONSTRAINT_NAME

GROUP BY CCL.TABLE_NAME , COLUMN_NAME ) CON

WHERE TBL.TABLE_NAME = TCM.TABLE_NAME

AND TBL.TABLE_NAME = TCL.TABLE_NAME

AND TCL.TABLE_NAME = CCM.TABLE_NAME AND TCL.COLUMN_NAME = CCM.COLUMN_NAME

AND TCL.TABLE_NAME = CON.TABLE_NAME(+) AND TCL.COLUMN_NAME = CON.COLUMN_NAME(+)

ORDER BY TBL.TABLE_NAME , COLUMN_ID