DB 테이블정보,컬럼정보 조회 쿼리(Tibero)

 

 

 select A.TABLE_NAME,
        A.COMMENTS,
        B.COLUMN_NAME,
        B.COL_COMMENTS,
        C.COL_NO,
        C.DATA_TYPE,
        C.DATA_LENGTH,
        D.CONSTRAINT_TYPE,
        D.POSITION,
        C.NOTNULL_YN
   from ( select table_name,
                 comments
            from ALL_TAB_COMMENTS
           where owner = '#유저명#') A,
        ( select TABLE_NAME,
                  COLUMN_NAME,
                  COMMENTS AS col_comments
             from all_col_comments
            where owner = '#유저명#') B,
        ( SELECT TABLE_NAME,
                  COLUMN_NAME,
                  TO_NUMBER(COLUMN_ID) +1 AS col_no,
                  DATA_TYPE,
                  DATA_LENGTH,
                  CASE WHEN NULLABLE = 'Y' THEN 'N' ELSE 'Y' END NOTNULL_YN
             FROM ALL_TAB_COLUMNS A
            WHERE owner = '#유저명#'
            ORDER BY TABLE_NAME,
                  COLUMN_NAME) C,
        ( SELECT A.CONSTRAINT_NAME,
                  A.TABLE_NAME,
                  A.COLUMN_NAME,
                  DECODE(B.CONSTRAINT_TYPE, 'P', 'Y', 'N') AS CONSTRAINT_TYPE,
                  A.POSITION+1 AS POSITION
             FROM USER_CONS_COLUMNS A RIGHT JOIN USER_CONSTRAINTS B ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
            where A.owner = '#유저명#'
              AND B.CONSTRAINT_TYPE = 'P'
            ORDER BY TABLE_NAME,
                  COLUMN_NAME,
                  CONSTRAINT_TYPE) D
  where A.table_name = B.table_name
    and B.table_name = C.table_name
    and B.COLUMN_NAME = C.COLUMN_NAME
    and B.table_name = D.table_name (+ )
    and B.COLUMN_NAME = D.COLUMN_NAME (+ )
    and A.COMMENTS is not null

#유저명# - 부분을 수정하여 사용하면 됩니다.테이블의 테이블설명, 컬럼,컬럼설명,dataType,제약설정, null여부 조회 쿼리입니다.