[Oracle] Table Object 정보 보기

[Oracle] Table Object 정보 보기

1. Table Layout(테이블 스키마)

SELECT A.TABLE_NAME,A.OWNER,NVL(C.COMMENTS, ' ') TABLE_DESC, A.COLUMN_ID, A.COLUMN_NAME, A.DATA_TYPE, CASE WHEN A.DATA_TYPE = 'NUMBER' AND A.DATA_SCALE = 0 THEN TO_CHAR(A.DATA_PRECISION) WHEN A.DATA_TYPE = 'NUMBER' AND A.DATA_SCALE > 0 THEN TO_CHAR(A.DATA_PRECISION) || ',' || TO_CHAR(A.DATA_SCALE) WHEN A.DATA_TYPE = 'DATE' THEN ' ' WHEN A.DATA_TYPE LIKE 'TIMESTAMP' THEN ' ' ELSE TO_CHAR(A.DATA_LENGTH) END DATA_LENGTH, (CASE WHEN D.CONSTRAINT_TYPE = 'P' THEN 'Y' END) PK, (CASE WHEN D.CONSTRAINT_TYPE = 'R' THEN 'Y' END) FK, CASE WHEN A.NULLABLE = 'Y' THEN 'NULL' ELSE ' ' END NULLABLE, A.DATA_LENGTH AS MAX_LEN, NVL(B.COMMENTS, ' ') AS COLUMN_DESC FROM ALL_TAB_COLUMNS A INNER JOIN ALL_COL_COMMENTS B ON B.OWNER = A.OWNER AND B.TABLE_NAME = A.TABLE_NAME AND B.COLUMN_NAME = A.COLUMN_NAME INNER JOIN USER_TAB_COMMENTS C ON C.TABLE_NAME = A.TABLE_NAME LEFT JOIN ( SELECT A.TABLE_NAME, A.COLUMN_NAME, B.CONSTRAINT_TYPE FROM USER_CONS_COLUMNS A INNER JOIN USER_CONSTRAINTS B ON B.CONSTRAINT_NAME = A.CONSTRAINT_NAME AND B.CONSTRAINT_TYPE IN ('P', 'R') ) D ON D.TABLE_NAME = A.TABLE_NAME AND D.COLUMN_NAME = A.COLUMN_NAME WHERE A.TABLE_NAME = '테이블명' ORDER BY A.COLUMN_ID ASC;

2. Table Owner 확인

SELECT OWNER,OBJECT_NAME TableName,OBJECT_TYPE FROM ALL_OBJECTS WHERE OBJECT_NAME = '테이블명'';

3. Table Index 확인

SELECT A.OWNER,A.TABLE_NAME,A.INDEX_NAME,A.UNIQUENESS,B.COLUMN_NAME,B.DESCEND,B.COLUMN_POSITION FROM ALL_INDEXES A LEFT JOIN ALL_IND_COLUMNS B ON B.INDEX_OWNER = A.OWNER AND B.INDEX_NAME = A.INDEX_NAME WHERE A.TABLE_NAME = '테이블명' ORDER BY A.INDEX_NAME, B.COLUMN_POSITION

4. Table PK 확인

SELECT A.OWNER,A.TABLE_NAME,A.COLUMN_NAME,A.POSITION,B.STATUS FROM ALL_CONS_COLUMNS A INNER JOIN ALL_CONSTRAINTS B ON B.OWNER = A.OWNER AND B.CONSTRAINT_NAME = A.CONSTRAINT_NAME AND B.CONSTRAINT_TYPE = 'P' WHERE A.TABLE_NAME = '테이블명';