테이블 정보
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;
테이블-컬럼 명세
- 테이블 컬럼 정보 보기
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;
테이블-인덱스 명세
- 테이블 인덱스 정보 보기
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;
테이블 참조키 정보 보기
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;
테이블 제약조건 정보
SELECT CONSTRAINT_NAME
, CONSTRAINT_TYPE
, TABLE_NAME
FROM SYS.USER_CONSTRAINTS
ORDER BY CONSTRAINT_NAME ASC;
테이블 트리거 정보
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;
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