oracle表名和列名改成小写,ORACLE表名与列名小写转成大写

批量将表名变为大写

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;

将特点表PROPERTY_INFO的所有列名小写变大写

begin

for c in (select COLUMN_NAME cn from all_tab_columns where table_name="PROPERTY_INFO") loop

begin

execute immediate "alter table PROPERTY_INFO rename column ""||c.cn||"" to "||c.cn;

exception

when others then

dbms_output.put_line("PROPERTY_INFO"||"."||c.cn||"已经存在");

end;

end loop;

end;

begin

DBMS_OUTPUT.ENABLE (buffer_size=>null) ;

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 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 "|| "UPPER"(c.tn);

exception

when others then

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

end;

end loop;

end;

-- 修改数据库表名大小写

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 ""||upper(c.tn)||""";

exception

when others then

-- 已经存在，输出提示

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

end;

end loop;

end;

select column_name cn from user_tab_columns where table_name="a"

alter table a RENAME COLUMN "B" to "b"

ALTER TABLE "IBANK"."a" RENAME COLUMN "b" TO "b";

-- 修改数据库字段名大小写

begin

-- 启动缓冲

DBMS_OUTPUT.ENABLE (buffer_size=>null) ;

-- 遍历所有表名

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 ""||upper(c.cn)||""";

-- 异常处理

exception

when others then

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

end;

end loop;

end;

end loop;

end;