How to rename UPPER CASE table and column names to lower case?

[Former Member]

We have a database with the original table and column names created in all UPPER CASE (influenced from back in the day when one of those other databases had to have upper case names...). I would like to clean it up by changing all these to lower case (much more readable, and consistent with all the newer tables).

But an ALTER...RENAME tells me the table or column already exists.

It feels pretty scary to rename to a slighlty different name and then back to the original in lowercase... what underlying and undetectable effects will this have...

Is there a safe way?

Thanks, Bill

[VolkerBarth]

Would a rebuild be feasible for you? - That should certainly do the trick.

[Former Member]

That is the only thing I could come up with. I would certainly rather generate an SQL script from the catalog tables, but rebuild over the Christmas holidays with a lot of editing of the script file might be the ticket.

[MarkCulp]

I was going to suggest the same option: (1) unload the database (to get reload.sql + dat files), (2) edit the reload.sql script to change the table names to lowercase, (3) dbinit a new database, and then (4) reload the new database using the revised reload.sql script.

[VolkerBarth]

Just to add from the docs:

Identifiers such as table names, column names, and so on, are case insensitive in SQL Anywhere databases.

I guess that's why the ALTER TABLE ... RENAME will fail even for case-sensitive databases here (although I guess it could have been implemented differently, i.e. to change the table/column name even if it compares as before "identifier-wise":)).

[Former Member]

yes, I LOVE that feature. Saves grief when various developers implement upper/lower/or no case PKs. And we all can still find the data.

I was just hoping it would work like Find/Replace: find "XyZ" replace with "xyz".

[Former Member · Accepted solution]

So, sounds like I haven't missed anything obvious. I guess my plan wll be: 1. Do an UNLOAD 2. Write a Stored Proc to use the catalog tables to pickup the uppercase names and then REPLACE the values in the script file. 3. Do the RELOAD.

And... get a nice tidy defragged database file in the process.

The DB is only 8GB in size, and we have a substantial server, so not really a big deal.

Thanks Volker and Mark.

[MarkCulp · Accepted solution]

FWIW: You may be able to easily update the reload.sql script using a perl script similar to:

#!perl -n
s/^CREATE (.*\\s)?TABLE "([^"]+)"\\."([^"]+)"/'CREATE '.$1.'TABLE "'.$2.'"."'.lc($3).'"'/se;
print;

If you were to name the above script file 'lc_table_names.pl' then you would run:

perl lc_table_names.pl reload.sql > reload_lc.sql

[Former Member · Accepted solution]

PERL script... should that be spelled PEARL? grn

I remember Breck writing years ago about people who own a hammer always use a hammer to solve every problem. That would be me: I know PowerBuilder and SQLA very well, and have ignored all other languages as a distraction. So I only have 2 hammers in my toolkit.

[VolkerBarth · Accepted solution]

Now, the inevitable xkcd link...

[Breck_Carter · Accepted solution]

> should that be spelled PEARL?

No, purl...

[VolkerBarth · Accepted solution]

So you are basically asking for a REGEXP_REPLACE SQL function in order to have something more - though not necessarily something "much more readable":) - in the familiar toolkit?

[Breck_Carter · Accepted solution]

No, let's not make it more complex than necessary... let's just ask for ALTER RENAME to work without checking for equality.

[VolkerBarth · Accepted solution]

Well, I guess I had forgot to add another ":)" to my only partially serious suggestion...

s/^CREATE (.*\\s)?TABLE "([^"]+)"."([^"]+)"/'CREATE '.$1.'TABLE "'.$2.'"."'.lc($3).'"'/se;

is not a regex I would claim to understand and certainly not one I could come up with myself...

That being said, for other (and simpler) use cases I would like to have a REGEXP_REPLACE(): If you can find patterns with REGEXP_SUBSTR(), it's sometimes unsatisfying if you cannot change them...