Oracle | 테이블 정의서 제작 쿼리
페이지 정보
작성자 100K5 작성일17-05-25 12:56 조회35,749회 댓글0건관련링크
본문
select
A1.TABLE_COMMENTS,
A1.TABLE_NAME,
A1.COLUMN_NAME,
(
case
when B1.CONSTRAINT_TYPE = 'P' then 'Y'
end
) PK_FLAG,
nvl(A1.NULL_FLAG, 'N') as NULL_FLAG,
A1.DATA_TYPE || (
case A1.DATA_TYPE
when 'NUMBER' then '(' || to_char(A1.DATA_LENGTH) || ',' || to_char(A1.DATA_PRECISION) || ')'
when 'DATE' then ' '
else '(' || A1.DATA_LENGTH || ')'
end
) DATA_TYPE,
A1.COLUMN_ID,
A1.COLUMN_COMMENTS
from
(
select
B.COMMENTS TABLE_COMMENTS,
A.TABLE_NAME TABLE_NAME,
C.COMMENTS COLUMN_COMMENTS,
A.COLUMN_NAME COLUMN_NAME,
(
case A.NULLABLE
when 'Y' then 'Y'
end
) NULL_FLAG,
A.DATA_TYPE DATA_TYPE,
A.DATA_LENGTH,
A.COLUMN_ID as COLUMN_ID,
A.DATA_PRECISION
from
USER_TAB_COLUMNS 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 B.TABLE_TYPE = 'TABLE'
) A1,
(
select
A.TABLE_NAME,
A.COLUMN_NAME,
B.CONSTRAINT_TYPE
from
USER_CONS_COLUMNS A,
USER_CONSTRAINTS B
where
(A.CONSTRAINT_NAME = B.CONSTRAINT_NAME)
and B.CONSTRAINT_TYPE in (
'P',
'R'
)
) B1
where
(
A1.TABLE_NAME = B1.TABLE_NAME(+)
and A1.COLUMN_NAME = B1.COLUMN_NAME(+)
)
--AND A1.TABLE_NAME LIKE 'VMS%'
order by
A1.TABLE_NAME,
A1.COLUMN_ID;
A1.TABLE_COMMENTS,
A1.TABLE_NAME,
A1.COLUMN_NAME,
(
case
when B1.CONSTRAINT_TYPE = 'P' then 'Y'
end
) PK_FLAG,
nvl(A1.NULL_FLAG, 'N') as NULL_FLAG,
A1.DATA_TYPE || (
case A1.DATA_TYPE
when 'NUMBER' then '(' || to_char(A1.DATA_LENGTH) || ',' || to_char(A1.DATA_PRECISION) || ')'
when 'DATE' then ' '
else '(' || A1.DATA_LENGTH || ')'
end
) DATA_TYPE,
A1.COLUMN_ID,
A1.COLUMN_COMMENTS
from
(
select
B.COMMENTS TABLE_COMMENTS,
A.TABLE_NAME TABLE_NAME,
C.COMMENTS COLUMN_COMMENTS,
A.COLUMN_NAME COLUMN_NAME,
(
case A.NULLABLE
when 'Y' then 'Y'
end
) NULL_FLAG,
A.DATA_TYPE DATA_TYPE,
A.DATA_LENGTH,
A.COLUMN_ID as COLUMN_ID,
A.DATA_PRECISION
from
USER_TAB_COLUMNS 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 B.TABLE_TYPE = 'TABLE'
) A1,
(
select
A.TABLE_NAME,
A.COLUMN_NAME,
B.CONSTRAINT_TYPE
from
USER_CONS_COLUMNS A,
USER_CONSTRAINTS B
where
(A.CONSTRAINT_NAME = B.CONSTRAINT_NAME)
and B.CONSTRAINT_TYPE in (
'P',
'R'
)
) B1
where
(
A1.TABLE_NAME = B1.TABLE_NAME(+)
and A1.COLUMN_NAME = B1.COLUMN_NAME(+)
)
--AND A1.TABLE_NAME LIKE 'VMS%'
order by
A1.TABLE_NAME,
A1.COLUMN_ID;
댓글목록
등록된 댓글이 없습니다.