Oracle 테이블 정보 추출

2011-07-28 17:36:30

---

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