How to change table and column name case.

[HenryMonster]

Hi All,

This may seem like a funny question, but it's really important...

I have a MS SQL database with 36 tables, which my boss wants to move to Oracle.
I tried to use MS SQL Enterprise manager tool: Export and Import and it does the job perfectly with just one caveat:

all the table names and all the column names in this MSSQL database are loiwer case; so when the export toll transfers
it to Oracle, it creates all tables and columns in lower case. AFter this I cannot simply run a SQL statement in Oracle without putting all table names and column names in double quotes, e.g.:

select "account_no", "name"
from "fees_accounts"

This is very annoying.

Of course I can go manually thru all 36 tables and their columns (in Design Table menu) and change everything to upper case.

Is there any way to do this more elegantly (anmd faster).
Any way to do this using MS SQL sys- tables ?


THANK YOU VERY MUCH !

 

[nigelrivett]

see sp_rename

something like

declare @tbl varchar(128), @newtbl varchar(128)
select name into #a from sysobjects where type = 'U'
select @tbl = ''
while @tbl < (select max(name) from #a
begin
select @tbl = min(name) from #a where name > @tbl
select @newtbl = upper(@tbl
exec sp_rename @tbl, @newtbl
end

Run it first without the exec to see what it will do.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

You want the columns as well

It's the same thing. Easier from information_schema.columns as you have the table and column names in the same table. See bol for the format of sp_rename for columns.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.