469,622 Members | 2,027 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,622 developers. It's quick & easy.

Converting tables to Upper case

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.

Jul 23 '05 #1
2 1930
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'

Jul 23 '05 #2
Thanks very much, this works a treat and is a great idea. Apologies
for delay in replying.

kevin.
"Thomas R. Hummel" <to********@hotmail.com> wrote in message news:<11**********************@f14g2000cwb.googleg roups.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'

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by ToddT | last post: by
10 posts views Thread by jose.jeria | last post: by
4 posts views Thread by programmerforhire | last post: by
3 posts views Thread by Brian Conway | last post: by
8 posts views Thread by Brian Conway | last post: by
6 posts views Thread by Manish | last post: by
5 posts views Thread by Nelson | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.