By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,522 Members | 1,943 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,522 IT Pros & Developers. It's quick & easy.

Converting tables to Upper case

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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.