[oracle] 테이블정의서 만들기 쿼리

--우선 코맨트를 전부 넣은 상태에서 실행해야 합니다.
COMMENT ON TABLE 스키마명.테이블명 IS '테이블설명';
COMMENT ON COLUMN 스키마명.테이블명.컬럼명 IS '컬럼설명';
-- 테이블 정의서 1번째 방법
SELECT A1.TABLE_COMMENTS
, A1.TABLE_NAME
-- , A1.COLUMN_ID
, A1.COLUMN_NAME AS COLUMN_ID
, A1.COLUMN_COMMENTS AS COLUMN_NAME
, A1.DATA_TYPE AS DATA_TYPE
, (CASE A1.DATA_TYPE WHEN 'NUMBER' THEN TO_CHAR(A1.DATA_LENGTH)
WHEN 'DATE' THEN ' '
ELSE TO_CHAR(A1.DATA_LENGTH) END) AS DATA_LENGTH
, NVL(A1.NULL_FLAG, 'N') AS NULL_FLAG
, (CASEWHEN B1.CONSTRAINT_TYPE = 'P' THEN 'PK' END) PK_FLAG
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


-- 테이블정으서 만들기 2번째 방법
SELECT
X.TABLE_NAME TABLE_ID,
(SELECT COMMENTS FROM USER_TAB_COMMENTS
WHERE TABLE_NAME = X.TABLE_NAME) TABLE_NM,
X.COLUMN_NAME FIELD_ID,
(SELECT COMMENTS FROM USER_COL_COMMENTS
WHERE TABLE_NAME = X.TABLE_NAME AND COLUMN_NAME = X.COLUMN_NAME) FIELD_NAME,
X.DATA_TYPE AS "TYPE",
DECODE(X.DATA_TYPE,
'DATE', NULL,
'BLOB', NULL,
X.DATA_LENGTH || CASE WHEN X.DATA_PRECISION IS NOT NULL THEN ',' || X.DATA_SCALE END )
DATATYPES,
CASE WHEN SUBSTR(Y.INDEX_NAME, 1, 2) = 'PK' THEN 'PK'
ELSE DECODE(X.NULLABLE, 'N', 'NOT NULL', '') END "NOT NULL",
X.DATA_DEFAULT
FROM COLS X, USER_IND_COLUMNS Y
WHERE X.TABLE_NAME = Y.TABLE_NAME(+) AND X.COLUMN_NAME = Y.COLUMN_NAME(+)
ORDER BY X.TABLE_NAME, X.COLUMN_ID

-- 테이블정으서 만들기 3번째 방법(주로 사용)
SELECT A1.TABLE_COMMENTS
, A1.TABLE_NAME
, A1.COLUMN_ID
, A1.COLUMN_NAME
, A1.COLUMN_COMMENTS
, (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
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


--테이블 목록 만들기(대략적인 크기로..)
SELECT TA1.TABLE_NAME AS TALBE_ID,
TA1.COMMENTS AS TABLE_NAME,
TA2.DATA_LENGTH AS DATA_LENGTH,
TA3.MAX_NUM AS MAX_NUM,
TA3.INITIAL_NUM AS INITIAL_NUM,
(TA2.DATA_LENGTH*TA3.MAX_NUM) AS EXTEND_NUM
FROM
(SELECT TABLE_NAME, COMMENTS FROM USER_TAB_COMMENTS WHERE TABLE_TYPE = 'TABLE') TA1,
(SELECT TABLE_NAME, SUM(DATA_LENGTH) AS DATA_LENGTH FROM USER_TAB_COLUMNS GROUP BY TABLE_NAME) TA2,
(SELECT TABLE_NAME
,(CASE
WHEN (NUM_ROWS < 1000) THEN 1000
WHEN (10000 > NUM_ROWS) AND (NUM_ROWS > 1000) THEN 10000
WHEN (100000 > NUM_ROWS) AND (NUM_ROWS > 10000) THEN 100000
WHEN (1000000 > NUM_ROWS) AND (NUM_ROWS > 100000) THEN 1000000
ELSE 10000000
END ) AS MAX_NUM
, INITIAL_EXTENT AS INITIAL_NUM
, GREATEST(4, CEIL(NUM_ROWS /
DECODE(((ROUND(((1958 - (INI_TRANS * 23)) * ((100 - PCT_FREE) /100)) /
DECODE(AVG_ROW_LEN,0,1,AVG_ROW_LEN))))*2,0,1,
((ROUND(((1958 - (INI_TRANS * 23)) * ((100 - PCT_FREE) /100)) /
DECODE(AVG_ROW_LEN,0,1,AVG_ROW_LEN))))*2)) * 2)
AS TABLESIZE_KBYTES
FROM USER_TABLES) TA3
WHERE TA1.TABLE_NAME = TA2.TABLE_NAME
AND TA1.TABLE_NAME = TA3.TABLE_NAME
ORDER BY TA1.TABLE_NAME