Subject Re: [firebird-support] Renaming tables, foreign key references
Author unordained
---------- Original Message -----------
From: "sqlsvr" <sqlsvr@...>
> Does anyone have a script to rename tables AND/OR foreign key
> references? (or can you tell me the structure of the system tables so
> I can write the script). I know the tables are stored in RDB$Database
> with a flag but changing the actual name causes the table to be corrupted.
------- End of Original Message -------

The solutions given to you in firebird-java stand:
- create a new table, copy the data (which you object to, because of the table
I agree a script would be useful here, for pointing FK's to the new table,
but unless you want to run a script that does a global text-replace on your
triggers and procedures, you'll still have manual work to do. As pointed out
previously, Oracle et al. simple de-activate your procedures and leave it up to
you to fix them, so that's no different.

- create a view to proxy access to the old table, to appear renamed
Even if you rename the physical table (in-place, or by recreating), you may
want an updateable view as a "backup", to save you in case there are client
apps with embedded SQL referencing this table that you didn't know about, or
don't have time to fix, or don't have access to fix.

If you decide to write your own scripts, you'll need the following information:
In particular, check rdb$dependencies to see what might need changing,
including views. Obviously won't tell you about any client-side SQL that will
break after rename.

If you don't have procedures, triggers, etc. to rebuild, then for this one case
it would probably be easier to just use flamerobin (or some other tool --
ibexpert, etc.) to extract the current DDL for the table, rename the table in
that script, and then recreate it. It already extracts FK's, permissions, etc.
for you and puts them in script form. Within that limited scope, a simple text-
replace would probaby be safe enough.

If you do find a solution by which you can rename the table in a sufficient
number of rdb$ tables to make it not fail (which, when I tried it, certainly
wasn't easy,) please do contribute back. I'm sure others have had the same
question. Maybe it could even be integrated into some of the gui tools.