반응형
alter table
table_name
add
(
column1_name column1_datatype column1_constraint,
column2_name column2_datatype column2_constraint
);
alter table
tb_cust
add
cust_type varchar2(2) NOT NULL;
ALTER TABLE
cust_table
ADD
(
cust_type char(2) NOT NULL,
cust_num number
);
SQL> alter table author add (last_update_date date);
SELECT A1.TABLE_COMMENTS TABLE_COMMENTS , A1.TABLE_NAME TABLE_NAME , A1.COLUMN_COMMENTS COLUMN_COMMENTS , A1.COLUMN_NAME COLUMN_NAME , (CASE WHEN B1.CONSTRAINT_TYPE = 'P' THEN 'Y' END) PK_FLAG , (CASE WHEN B1.CONSTRAINT_TYPE = 'R' THEN 'Y' END) FK_FLAG , A1.NULL_FLAG , A1.DATA_TYPE , A1.DATA_LENGTH FROM (SELECT B.COMMENTS TABLE_COMMENTS , A.TABLE_NAME TABLE_NAME , C.COMMENTS COLUMN_COMMENTS , A.COLUMN_NAME COLUMN_NAME , (CASE A.NULLABLE WHEN 'Y' THEN 'Y' END) NULL_FLAG , A.DATA_TYPE DATA_TYPE , (CASE WHEN A.DATA_TYPE IN ('CHAR', 'VARCHAR2') THEN '(' || A.DATA_LENGTH || ')' WHEN A.DATA_TYPE = 'NUMBER' AND A.DATA_SCALE = 0 AND A.DATA_PRECISION IS NOT NULL THEN '(' || A.DATA_PRECISION || ')' WHEN A.DATA_TYPE = 'NUMBER' AND A.DATA_SCALE <> 0 THEN '(' || A.DATA_PRECISION || ',' || A.DATA_SCALE || ')' END ) DATA_LENGTH , A.COLUMN_ID FROM USER_TAB_COLUMNS A , USER_TAB_COMMENTS B , USER_COL_COMMENTS C WHERE (A.TABLE_NAME = B.TABLE_NAME) AND ( A.TABLE_NAME = C.TABLE_NAME AND A.COLUMN_NAME = C.COLUMN_NAME )) A1 , (SELECT A.TABLE_NAME , A.COLUMN_NAME , B.CONSTRAINT_TYPE FROM USER_CONS_COLUMNS A , USER_CONSTRAINTS B WHERE (A.CONSTRAINT_NAME = B.CONSTRAINT_NAME) AND B.CONSTRAINT_TYPE IN ('P', 'R')) B1 WHERE ( A1.TABLE_NAME = B1.TABLE_NAME(+) AND A1.COLUMN_NAME = B1.COLUMN_NAME(+)) ORDER BY A1.TABLE_NAME, A1.COLUMN_ID |
SELECT X.TABLE_NAME ,X.TABLE_COMMENTS ,X.COLUMN_NAME ,X.COLUMN_COMMENTS ,X.PK_FLAG ,X.FK_FLAG ,X.NULL_FLAG ,X.DATA_TYPE || X.DATA_LENGTH as COLUMN_DATA_TYPE FROM (SELECT A1.TABLE_NAME TABLE_NAME ,A1.TABLE_COMMENTS TABLE_COMMENTS ,A1.COLUMN_NAME COLUMN_NAME ,A1.COLUMN_COMMENTS COLUMN_COMMENTS ,(CASE WHEN B1.CONSTRAINT_TYPE = 'P' THEN 'Y' END) PK_FLAG ,(CASE WHEN B1.CONSTRAINT_TYPE = 'R' THEN 'Y' END) FK_FLAG ,A1.NULL_FLAG ,A1.DATA_TYPE ,A1.DATA_LENGTH ,A1.COLUMN_ID FROM (SELECT B.COMMENTS TABLE_COMMENTS ,A.TABLE_NAME TABLE_NAME ,C.COMMENTS COLUMN_COMMENTS ,A.COLUMN_NAME COLUMN_NAME ,(CASE A.NULLABLE WHEN 'Y' THEN 'Y' END) NULL_FLAG ,A.DATA_TYPE DATA_TYPE ,(CASE WHEN A.DATA_TYPE IN ('CHAR', 'VARCHAR2') THEN '(' || A.DATA_LENGTH || ')' WHEN A.DATA_TYPE = 'NUMBER' AND A.DATA_SCALE = 0 AND A.DATA_PRECISION IS NOT NULL THEN '(' || A.DATA_PRECISION || ')' WHEN A.DATA_TYPE = 'NUMBER' AND A.DATA_SCALE <> 0 THEN '(' || A.DATA_PRECISION || ',' || A.DATA_SCALE || ')' END) DATA_LENGTH ,A.COLUMN_ID FROM USER_TAB_COLUMNS A ,USER_TAB_COMMENTS B ,USER_COL_COMMENTS C WHERE (A.TABLE_NAME = B.TABLE_NAME) AND (A.TABLE_NAME = C.TABLE_NAME AND A.COLUMN_NAME = C.COLUMN_NAME -- AND A.TABLE_NAME like 'DIM%' /* [조건] 원하는 테이블만 조회 한다. */ )) A1 ,(SELECT A.TABLE_NAME, A.COLUMN_NAME, B.CONSTRAINT_TYPE FROM USER_CONS_COLUMNS A ,USER_CONSTRAINTS B WHERE (A.CONSTRAINT_NAME = B.CONSTRAINT_NAME) AND B.CONSTRAINT_TYPE IN ('P', 'R')) B1 WHERE (A1.TABLE_NAME = B1.TABLE_NAME(+) AND A1.COLUMN_NAME = B1.COLUMN_NAME(+))) X ORDER BY X.TABLE_NAME, X.COLUMN_ID 출처: https://estenpark.tistory.com/334 [DATA 전문가로 가는 길] |
반응형
'개발자정보' 카테고리의 다른 글
The Top Html5 Canvas 오픈소스 프로젝트 (0) | 2021.06.20 |
---|---|
visual studio code spring boot multiple datasource (0) | 2021.06.01 |
Linux Bash comman, A-Z Index (0) | 2021.05.29 |
Top 25 free RPA tools (0) | 2021.05.26 |
spring boot mariadb 연동하기 (0) | 2021.05.25 |