Anonymous wrote:
I was wondering if there is a way to alter a table column, to use a domain with has the exact same definition, when the table is used by views; the view is used by stored procs so droping the view is a major task.
Pavel Cisar answers:
Yes, there is a way how to do it.
Make a backup of your database. It's important step, as we're going to make direct modifications to system tables, so it's good to be safe.
Make sure that nobody is using the database. If it's a production database, the best approach is to take it off-line (using gfix or other administration tool).
Use your favorite database administration tool and attach to the database as SYSDBA.
Create a new domain with the same datatype as the column has.
When you attempt to alter the table to use the new domain, there is an error message about dependency that mention column name in format RDB$ (for example RDB$79). Remember this name.
Alternatively, you can locate this name in table rdb$relation_field. Look for row where column rdb$relation_name = your base table name and rdb$field_name = column name that you want to redefine. The system domain name is in column rdb$field_source on this row.
execute next SQL statement:
update rdb$relation_fields set rdb$field_source = 'you new domain name' where rdb$field_source = 'domain name from error message'
If you're not using delimited identifiers, then column names must be in upper case!
After commit, your table will use new domain, so the old system created domain is no longer necessary (it's an orphan in fact) so you can delete it executing:
delete from rdb$fields where rdb$field_name = 'RDB$'
Before you'll do that on your database, try it first on newly created database with some test table and view.