Converting tables to Upper case

[kevin]

Hello, we've an Oracle transition in the pipeline and want to convert
all our database objects to upper case. Any one got a script or
technique (other than manual) to do it?

Many thanks, Kevin.

[Thomas R. Hummel]

The two statements below should get you started. If you need to rename
indexes in addition to tables then you can change the u.type in the
WHERE clause of the first statement. Run these statements in every
database in which you need to do this. It will generate the code that
you need to run, so copy and paste the results in the query window and
run that.

I ran both statements and eyeballed the results and they looked ok, but
I have not actually tested this by running the results, so you should
go over it yourself as well.

HTH,
-Tom.

SELECT 'EXEC sp_rename ''[' + u.name + '].[' + o.name + ']'', ''[' +
UPPER(o.name) + ']'''
FROM sysobjects o
INNER JOIN sysusers u ON u.uid = o.uid
WHERE o.type = 'U'

SELECT 'EXEC sp_rename ''[' + u.name + '].[' + t.name + '].[' + c.name
+ ']'', ''[' + UPPER(c.name) + ']'', ''COLUMN'''
FROM sysobjects t
INNER JOIN sysusers u ON u.uid = t.uid
INNER JOIN syscolumns c ON c.id = t.id
WHERE t.type = 'U'

[Kevin Munro]

Thanks very much, this works a treat and is a great idea. Apologies
for delay in replying.

kevin.
"Thomas R. Hummel" <to********@hot mail.com> wrote in message news:<11******* *************** @f14g2000cwb.go oglegroups.com> ...

The two statements below should get you started. If you need to rename
indexes in addition to tables then you can change the u.type in the
WHERE clause of the first statement. Run these statements in every
database in which you need to do this. It will generate the code that
you need to run, so copy and paste the results in the query window and
run that.

I ran both statements and eyeballed the results and they looked ok, but
I have not actually tested this by running the results, so you should
go over it yourself as well.

HTH,
-Tom.

SELECT 'EXEC sp_rename ''[' + u.name + '].[' + o.name + ']'', ''[' +
UPPER(o.name) + ']'''
FROM sysobjects o
INNER JOIN sysusers u ON u.uid = o.uid
WHERE o.type = 'U'

SELECT 'EXEC sp_rename ''[' + u.name + '].[' + t.name + '].[' + c.name
+ ']'', ''[' + UPPER(c.name) + ']'', ''COLUMN'''
FROM sysobjects t
INNER JOIN sysusers u ON u.uid = t.uid
INNER JOIN syscolumns c ON c.id = t.id
WHERE t.type = 'U'