oracle将表名和字段名变为大写

  当使用powerdesigner创建数据库时要注意大小写。

  注:以下脚本在oracle 10g,11g上正确执行

  批量将表名变为大写

  begin

  for c in (select table_name tn from user_tables where table_name <> upper(table_name)) loop

  begin

  execute immediate ‘alter table "’||c.tn||’" rename to ‘||c.tn;

  exception

  when others then

  dbms_output.put_line(c.tn||’已存在’);

  end;

  end loop;

  end;

  批量将空间内所有表的所有字段名变成大写

  begin

  for t in (select table_name tn from user_tables) loop

  begin

  for c in (select column_name cn from user_tab_columns where table_name=t.tn) loop

  begin

  execute immediate ‘alter table "’||t.tn||’" rename column "’||c.cn||’" to ‘||c.cn;

  exception

  when others then

  dbms_output.put_line(t.tn||’.’||c.cn||’已经存在’);

  end;

  end loop;

  end;

  end loop;

  end;

  将用户空间的所有表名及所有字段变为大写

  begin

  for t in (select table_name tn from user_tables where table_name <> upper(table_name)) loop

  begin

  for c in (select column_name cn from user_tab_columns where table_name=t.tn) loop

  begin

  execute immediate ‘alter table "’||t.tn||’" rename column "’||c.cn||’" to ‘||c.cn;

  exception

  when others then

  dbms_output.put_line(t.tn||’.’||c.cn||’已经存在’);

  end;

  end loop;

  execute immediate ‘alter table "’||t.tn||’" rename to ‘||t.tn;

  exception

  when others then

  dbms_output.put_line(t.tn||’已存在’);

  end;

  end loop;

  end;

发表评论

电子邮件地址不会被公开。 必填项已用*标注