행위

오라클 테이블 정보

DB CAFE

thumb_up 추천메뉴 바로가기

1 테이블 정보[편집]

SELECT a.OBJECT_ID, a.OBJECT_TYPE, a.OBJECT_NAME
     , TO_CHAR(CREATED, 'YYYY-MM-DD HH24:MI:SS') AS CREATED
     , TO_CHAR(LAST_DDL_TIME, 'YYYY-MM-DD HH24:MI:SS') AS LAST_DDL_TIME
     , b.COMMENTS 
  FROM SYS.USER_OBJECTS a
     , SYS.USER_TAB_COMMENTS b 
 WHERE (a.OBJECT_TYPE = 'TABLE' OR a.OBJECT_TYPE = 'VIEW') 
   AND a.OBJECT_NAME = b.TABLE_NAME 
 ORDER BY a.OBJECT_TYPE ASC
        , a.OBJECT_NAME ASC;

2 테이블-컬럼 명세[편집]

  1. 테이블 컬럼 정보 보기
SELECT a.OBJECT_ID, a.OBJECT_TYPE, a.OBJECT_NAME, b.COLUMN_ID, b.COLUMN_NAME
     , b.DATA_TYPE 
     , (CASE b.DATA_TYPE WHEN 'NUMBER' THEN TO_CHAR(b.DATA_LENGTH) 
                        WHEN 'DATE' THEN ' ' 
                        ELSE TO_CHAR(b.DATA_LENGTH) END) AS DATA_LENGTH
     , b.DATA_TYPE || (CASE b.DATA_TYPE WHEN 'NUMBER' THEN '(' || TO_CHAR(b.DATA_LENGTH) || ',' || TO_CHAR(b.DATA_PRECISION) || ')'
                                        WHEN 'DATE' THEN ' ' 
                                        ELSE '(' || b.DATA_LENGTH || ')' 
                        END ) DATA_TYPE_DESC
     , DECODE(b.NULLABLE, 'N', 'Y', '') NOT_NULL
     , b.DATA_DEFAULT, c.COMMENTS
  FROM SYS.USER_OBJECTS a, SYS.USER_TAB_COLUMNS b, SYS.USER_COL_COMMENTS c
 WHERE (a.OBJECT_TYPE = 'TABLE' OR a.OBJECT_TYPE = 'VIEW') 
   AND a.OBJECT_NAME = b.TABLE_NAME 
   AND b.TABLE_NAME= c.TABLE_NAME 
   AND b.COLUMN_NAME = c.COLUMN_NAME
 ORDER BY a.OBJECT_TYPE ASC, a.OBJECT_NAME ASC, b.COLUMN_ID ASC;

3 테이블-인덱스 명세[편집]

  1. 테이블 인덱스 정보 보기
SELECT a.TABLE_NAME, a.INDEX_NAME, b.UNIQUENESS, a.COLUMN_POSITION, a.COLUMN_NAME, a.DESCEND
FROM SYS.USER_IND_COLUMNS a, SYS.USER_INDEXES b
WHERE a.INDEX_NAME = b.INDEX_NAME
ORDER BY a.TABLE_NAME ASC, a.INDEX_NAME ASC, a.COLUMN_POSITION ASC;

4 테이블 참조키 정보 보기[편집]

SELECT a.CONSTRAINT_NAME "Foreign Key"
, a.CONSTRAINT_TYPE
, a.TABLE_NAME "Foreign Key TableName"
, (SELECT COLUMN_NAME FROM SYS.USER_CONS_COLUMNS WHERE CONSTRAINT_NAME = a.CONSTRAINT_NAME) "Foreign
Key ColumnName"
, b.CONSTRAINT_NAME "Referenced Key"
, b.CONSTRAINT_TYPE
, b.TABLE_NAME "Referenced Key TableName"
, (SELECT COLUMN_NAME FROM SYS.USER_CONS_COLUMNS WHERE CONSTRAINT_NAME = b.CONSTRAINT_NAME)
"Referenced Key ColumnName"
FROM SYS.USER_CONSTRAINTS a, SYS.USER_CONSTRAINTS b
WHERE a.CONSTRAINT_TYPE = 'R' AND a.R_CONSTRAINT_NAME = b.CONSTRAINT_NAME
ORDER BY a.CONSTRAINT_NAME ASC;

5 테이블 제약조건 정보[편집]

SELECT CONSTRAINT_NAME
     , CONSTRAINT_TYPE
     , TABLE_NAME 
  FROM SYS.USER_CONSTRAINTS
 ORDER BY CONSTRAINT_NAME ASC;

6 테이블 트리거 정보[편집]

SELECT OBJECT_ID
     , OBJECT_TYPE, OBJECT_NAME
     , TO_CHAR(CREATED, 'YYYY-MM-DD HH24:MI:SS') AS CREATED
     , TO_CHAR(LAST_DDL_TIME, 'YYYY-MM-DD HH24:MI:SS') AS LAST_DDL_TIME
  FROM SYS.USER_OBJECTS
 WHERE OBJECT_TYPE = 'TRIGGER'
 ORDER BY OBJECT_NAME ASC;

7 PK 없는 테이블 조회[편집]

select  OWNER, TABLE_NAME 
from    dba_tables dt 
where  not exists ( 
        select  'TRUE' 
        from    dba_constraints dc 
        where  dc.TABLE_NAME = dt.TABLE_NAME 
        and    dc.CONSTRAINT_TYPE='P') 
and        OWNER not in ('SYS','SYSTEM') 
order        by OWNER, TABLE_NAME
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=오라클_테이블_정보&oldid=3291"