출처 : http://seobangnim.com/zbxe/14685
Select TBL.TABLE_NAME, TCM.COMMENTS, TBL.TABLESPACE_NAME, TCL.COLUMN_ID "No", TCL.COLUMN_NAME "컬럼ID", Case When TCL.DATA_TYPE = 'VARCHAR2' Or TCL.DATA_TYPE = 'CHAR'Then TCL.DATA_TYPE || '(' || DATA_length || ')'When TCL.DATA_TYPE = 'NUMBER' And data_precision > 0 And data_scale > 0Then TCL.DATA_TYPE || '(' || data_precision || ',' || data_scale || ')'When TCL.DATA_TYPE = 'NUMBER' And data_precision > 0Then TCL.DATA_TYPE || '(' || data_precision || ')'When TCL.DATA_TYPE = 'NUMBER'Then TCL.DATA_TYPE || '()'Else TCL.DATA_TYPEEnd As "데이타타입", CON.KEY, Decode(Nullable , 'N' , 'No' , 'Yes') As "Null", DATA_DEFAULT "Default", CCM.COMMENTS "컬럼명", '' "비고"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 , 'R' , 1 , 0)) > 0Then 'PK,FK'When Sum(Decode(CONSTRAINT_TYPE , 'P' , 1 , 0)) > 0Then 'PK'When Sum(decode(CONSTRAINT_TYPE , 'R' , 1 , 0)) > 0Then 'FK'Else ''End As KEY, Sum(Decode(CONSTRAINT_TYPE , 'C' , 0 , 'P' , 0 , 'R' , 0 , 1)) As CCCFrom USER_CONS_COLUMNS CCL , USER_CONSTRAINTS CNSWhere CCL.CONSTRAINT_NAME = CNS.CONSTRAINT_NAMEGroup By CCL.TABLE_NAME , COLUMN_NAME) CONWhere TBL.TABLE_NAME = TCM.TABLE_NAMEAnd TBL.TABLE_NAME = TCL.TABLE_NAMEAnd TCL.TABLE_NAME = CCM.TABLE_NAMEAnd TCL.COLUMN_NAME = CCM.COLUMN_NAMEAnd TCL.TABLE_NAME = CON.TABLE_NAME(+)And TCL.COLUMN_NAME = CON.COLUMN_NAME(+)Order By TBL.TABLE_NAME , COLUMN_ID
1277430636_oracle테이블명세서쿼리.sql
1277430636_oracle테이블명세서쿼리.sql