2.0 KiB
테이블 정의서 형식(컬럼명, type, null, pk, comments)
select NLS_LOWER(a.column_name),data_type||'('||data_length||')' as dtype, decode(nullable,'Y','',nullable) as nullv ,max(b.POSITION) as pk, comments from USER_TAB_COLUMNS a, ALL_CONS_COLUMNS b,ALL_COL_COMMENTS c where a.table_name = b.table_name(+) and a.column_name = b.column_name(+) and a.table_name = c.table_name and a.column_name = c.column_name and a.table_name = 'MOBILE_CSORDER_MASTER' group by a.column_name,data_type, data_length, nullable, column_id,comments order by column_id
SELECT A.TABLE_NAME AS TABLE_NAME,
A.TAB_CMT AS 테이블설명,
A.COLUMN_NAME AS 컬럼명,
B.POS AS PK,
A.COL_CMT AS 컬럼설명,
A.DATA_TYPE AS 데이터유형,
A.데이터길이,
A.NULLABLE AS NULL여부,
A.COLUMN_ID AS 컬럼순서,
A.DATA_DEFAULT AS 기본값
FROM
(SELECT S1.TABLE_NAME,
S3.COMMENTS AS TAB_CMT,
S1.COLUMN_NAME,
S2.COMMENTS AS COL_CMT,
S1.DATA_TYPE,
CASE WHEN S1.DATA_PRECISION IS NOT NULL THEN DATA_PRECISION||','||DATA_SCALE
ELSE TO_CHAR(S1.DATA_LENGTH)
END AS 데이터길이,
NULLABLE,
COLUMN_ID,
DATA_DEFAULT
FROM USER_TAB_COLUMNS S1,
USER_COL_COMMENTS S2,
USER_TAB_COMMENTS S3
WHERE S1.TABLE_NAME = S2.TABLE_NAME
AND S1.COLUMN_NAME = S2.COLUMN_NAME
AND S2.TABLE_NAME = S3.TABLE_NAME ) A,
(SELECT T1.TABLE_NAME, T2.COLUMN_NAME, 'PK'||POSITION AS POS
FROM (SELECT TABLE_NAME, CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'P' )T1,
(SELECT TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME, POSITION
FROM USER_CONS_COLUMNS ) T2
WHERE T1.TABLE_NAME = T2.TABLE_NAME
AND T1.CONSTRAINT_NAME = T2.CONSTRAINT_NAME ) B
WHERE A.TABLE_NAME = B.TABLE_NAME(+)
AND A.COLUMN_NAME = B.COLUMN_NAME(+)
ORDER BY A.TABLE_NAME, A.COLUMN_ID