[ORACLE 4-1장] 테이블 정보 조회

1. 테이블 생성 후 테이블 정보 조회 

   - 조회 항목 

-- TABLE 정보 
SELECT DTCC.COMMENTS T_COMMENTS
        , DTC.TABLE_NAME
		, DTC.COLUMN_NAME
        , CASE 
            WHEN DATA_TYPE IN ('VARCHAR','VARCHAR2','CHAR') THEN DATA_TYPE||'('|| CHAR_LENGTH||CASE WHEN CHAR_USED = 'B' THEN ' BYTE' WHEN CHAR_USED = 'C' THEN ' CHAR' ELSE '' END||')'
            WHEN DATA_TYPE = 'NUMBER' AND DATA_PRECISION IS NOT NULL THEN DATA_TYPE||'('||DATA_PRECISION||','||DECODE(DATA_SCALE,0,'0',DATA_SCALE)||')'
            ELSE DATA_TYPE
          END DATA_TYPE
		, NULLABLE
		, DATA_DEFAULT
		, DCC.COMMENTS C_COMMENTS		
        , CASE WHEN DCCC.CONSTRAINT_NAME IS NOT NULL THEN DC.CONSTRAINT_TYPE ELSE '' END CONSTRAINT_NAME
        , DCCC.CONSTRAINT_NAME
        , DCCC.COLUMN_NAME 
        , DCCC.POSITION
FROM DBA_TABLES DT
INNER JOIN DBA_TAB_COLUMNS DTC ON DT.OWNER = DTC.OWNER AND DT.TABLE_NAME = DTC.TABLE_NAME
LEFT OUTER JOIN DBA_TAB_COMMENTS DTCC ON DT.OWNER = DTCC.OWNER AND DT.TABLE_NAME = DTCC.TABLE_NAME 
LEFT OUTER JOIN DBA_COL_COMMENTS DCC ON DTC.OWNER = DCC.OWNER AND DTC.TABLE_NAME = DCC.TABLE_NAME AND DTC.COLUMN_NAME = DCC.COLUMN_NAME 
LEFT OUTER JOIN DBA_CONSTRAINTS DC ON DT.OWNER = DC.OWNER AND DT.TABLE_NAME = DC.TABLE_NAME AND CONSTRAINT_TYPE = 'P'
LEFT OUTER JOIN DBA_CONS_COLUMNS  DCCC ON DC.OWNER = DCCC.OWNER AND DC.TABLE_NAME = DCCC.TABLE_NAME AND DC.CONSTRAINT_NAME = DCCC.CONSTRAINT_NAME AND DCC.COLUMN_NAME = DCCC.COLUMN_NAME
WHERE 1= 1 
--AND DT.OWNER = 'KDEX'
AND DT.TABLE_NAME LIKE 'BOARD'
--AND DCC.COMMENTS LIKE '%%'
--AND DTCC.COMMENTS LIKE '%추천%'
ORDER BY DTC.TABLE_NAME, DTC.COLUMN_ID
;

 

테이블 정보 조회시 화면

테이블 정보 조회