1.创建临时表,用来存储生成的Sql语句,同时依据表名和表注释决策是否执行增加操作
create table TEMP_TABLE
(
TABLE_NAME VARCHAR2(64),
TABLE_COMMENTS VARCHAR2(128),
OP_SENTENCE VARCHAR2(128)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
2.执行以下语句:
select t.table_name,t1.comments,'alter table ' || t.table_name || ' add (ORG_DEPT_ID NUMBER(12),PROJECT_ID NUMBER(12))'
from dba_tables t,dba_tab_comments t1 where t.owner = 'CGGCMCMS' and t.table_name = t1.table_name
and t1.owner = t.owner
3.将结果导出到Excel,讨论,执行