블로그 이미지
잡초 개발자의 좌충우돌 이야기 yysvip

카테고리

분류 전체보기 (211)
Java Programing (24)
Web Programing (20)
Database (36)
Tool (46)
까칠한 IT (27)
까칠한 정보 (42)
까칠한 Strory (16)
까칠한 Project (0)
Total906,274
Today3,757
Yesterday7,350

달력

« » 2018.10
  1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31      

공지사항

테이블 정의서 만들때 쓰는 쿼리

  까칠한 남자 Strory       yysvip.tistory.com       잡초 개발자 까찰한 쑤의 좌충우돌 개발 이야기

 

■ 우선 코맨트를 전부 넣은 상태에서 실행해야 합니다.
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                              
       , (CASE WHEN 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 
Posted by yysvip

최근에 달린 댓글

최근에 받은 트랙백

글 보관함