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

asked 14 Dec '15, 14:51

Bill%20Aumen's gravatar image

Bill Aumen
2.0k314673
accept rate: 16%

1

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

(14 Dec '15, 16:11) Volker Barth

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.

(14 Dec '15, 16:18) Bill Aumen
1

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.

(14 Dec '15, 16:18) Mark Culp
1

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":)).

(15 Dec '15, 06:07) Volker Barth

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".

(15 Dec '15, 11:08) Bill Aumen

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.

permanent link

answered 14 Dec '15, 16:28

Bill%20Aumen's gravatar image

Bill Aumen
2.0k314673
accept rate: 16%

1

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
(14 Dec '15, 16:55) Mark Culp
1

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.

(14 Dec '15, 17:00) Bill Aumen
Replies hidden

Now, the inevitable xkcd link...

(15 Dec '15, 02:49) Volker Barth
2

> should that be spelled PEARL?

No, purl...

(15 Dec '15, 07:42) Breck Carter

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?

(16 Dec '15, 01:20) Volker Barth

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

(16 Dec '15, 09:16) Breck Carter

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...

(16 Dec '15, 09:45) Volker Barth
showing 2 of 7 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×28
×10
×1

question asked: 14 Dec '15, 14:51

question was seen: 761 times

last updated: 16 Dec '15, 09:47