[Oracle SQL] 테이블 정의서 query

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

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

등록일시 : 2013.07.10 11:46 (업로드 2013.07.10 11:46)