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:
- creating a new table with a temporary name;
- populating it with data via INSERT from a SELECT of the old table;
- dropping the old table;
- creating a new table with the desired name;
- populating it with the data from the temporary table (step #2 above)
- 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:
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 *)
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?
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.
Post a Comment