Tuesday, October 26, 2010

How to Rename Tables in Firebird

I've encountered several instances in the past wherein I needed to rename a table in Firebird/Interbase. Since there seem to be no direct SQL command to do this, I would normally go through the roundabout solution of:
  1. creating a new table with a temporary name;
  2. populating it with data via INSERT from a SELECT of the old table;
  3. dropping the old table;
  4. creating a new table with the desired name;
  5. populating it with the data from the temporary table (step #2 above)
  6. dropping the temporary table
Its a very tedious process. I recently came across a solution that modifies the system tables directly to achieve the same result in 2 commands:

UPDATE RDB$RELATIONS SET RDB$RELATION_NAME='NEWNAME' where
RDB$RELATION_NAME='OLDNAME';

UPDATE RDB$RELATION_FIELDS SET RDB$RELATION_NAME='NEWNAME' where
RDB$RELATION_NAME='OLDNAME' and RDB$SYSTEM_FLAG=0;

That's all there is to it!

3 comments:

orly_andico said...

But.. but.. but.. that's not standard SQL!

The easiest way to "do" this in standard SQL would be

CREATE VIEW new_table_name AS SELECT column1, column2, column3 FROM old_table_name;

(better to enumerate the column names rather than rely on *)

Dick Chiang said...

Your solution doesn't really rename it either. You're just creating a new alias. That's cheating too! :D

BTW, if you create a view, then later on create more indices on the main table, does the view actually get to make use of those indices as well?

Customer Service said...

I tried this. I had some triggers associated with my table and they disappeared after the rename. In terms of stored procedures, I expected to need to manually edit them, that's fine. Overall this is a great shortcut - thank you.