Oracle 테이블 명세서 추출

2011-08-02 14:11:34

---

SQL 4 Oracle 오라클용 sql 실무

▒ Oracle 테이블 정보 추출

 

▒ 테이블 명세서 추출
SELECT a.TABLE_NAME, c.COMMENTS, a.COLUMN_NAME, b.COMMENTS, a.DATA_TYPE, a.DATA_LENGTH, a.NULLABLE
FROM  USER_TAB_COLUMNS a, USER_COL_COMMENTS b, USER_TAB_COMMENTS c
where a.TABLE_NAME=b.TABLE_NAME

    and b.TABLE_NAME=c.TABLE_NAME

    and a.COLUMN_NAME=b.COLUMN_NAME
order by a.TABLE_NAME,a.COLUMN_ID

 

▒특정 컬럼명이 있는건 추출
SELECT a.TABLE_NAME, c.COMMENTS, a.COLUMN_NAME, b.COMMENTS, a.DATA_TYPE, a.DATA_LENGTH, a.NULLABLE
FROM  USER_TAB_COLUMNS a,USER_COL_COMMENTS b,USER_TAB_COMMENTS c
where a.TABLE_NAME=b.TABLE_NAME
    and b.TABLE_NAME=c.TABLE_NAME
    and a.COLUMN_NAME=b.COLUMN_NAME
    and b.COMMENTS like '%조사%'
order by a.TABLE_NAME,a.COLUMN_ID