Database/ORACLE
DESC 를 SELECT로 구현
하이주니
2009. 3. 7. 00:01
반응형
SELECT
A.COLUMN_NAME "컬럼명",
A.COLUMN_ID "열순번",
(
-- 주키 번호를 읽어온다.(테이블명, 컬럼명 같은 필드의), INDEX가 여러개 선언시 UNIQUE한것으로 처리
SELECT COLUMN_POSITION FROM
USER_IND_COLUMNS KK
WHERE KK.TABLE_NAME=A.TABLE_NAME
AND KK.COLUMN_NAME=A.COLUMN_NAME
AND KK.INDEX_NAME=(SELECT MM.INDEX_NAME FROM USER_INDEXES MM WHERE MM.TABLE_NAME=KK.TABLE_NAME AND MM.UNIQUENESS = 'UNIQUE' AND LOGGING='YES'
AND MM.LEAF_BLOCKS = (SELECT MIN(LEAF_BLOCKS) FROM USER_INDEXES WHERE MM.TABLE_NAME=TABLE_NAME AND UNIQUENESS='UNIQUE' )
)
) "주키",
A.NULLABLE "NULL여부",
DECODE(A.DATA_TYPE,'DATE',A.DATA_TYPE,A.DATA_TYPE || ' (' || DATA_LENGTH || ' Byte)') "데이터 형",
A.DATA_DEFAULT "기본값" ,
C.COMMENTS "한글필드명"
FROM COLS A, USER_TAB_COMMENTS B, USER_COL_COMMENTS C
WHERE A.TABLE_NAME=B.TABLE_NAME
AND A.TABLE_NAME=C.TABLE_NAME
AND A.COLUMN_NAME=C.COLUMN_NAME
AND A.TABLE_NAME = UPPER('테이블명')
ORDER BY A.COLUMN_ID
반응형