stegt wrote:
An odd request maybe, but we have a requirement where we need to alter a table which has an unknown quantity of char(100) columns to be char(255) columns.
All the columns are prefixes 'F' then a number. The customer can add additional columns to the table at will, so we need to be able to go through a table and update all the char(100) columns to char(255).
I don't suppose there is some SQL black magic that can be used to do this?
The only way I can think of is to somehow extract the metadata from the table, then somehow find all the char(100) columns and run SQL for each column to update it, but that would mean writing an application that the customer would need to run to do the work, rather than just supplying a SQL script to do the work.
Any ideas?
Svein Erling Tysvær answers:
MAYBE this will create a usable script:
select 'alter table ' || rf.rdb$relation_name || ' alter ' || rf.rdb$field_name || ' type char(255);'
from rdb$fields f
join rdb$relation_fields rf on f.rdb$field_name = rf.rdb$field_source
join rdb$relations r on rf.rdb$relation_name = r.rdb$relation_name
where r.rdb$view_blr is null
and (r.rdb$system_flag is null or r.rdb$system_flag = 0)
and f.rdb$field_length = 100
order by rf.rdb$relation_name, rf.rdb$field_position;
I wrote maybe with capital letters because I don't know too much about the system tables, haven't tried it at all, don't know about side effects (what if there are VARCHAR(100) or other types with the same field length - you might not want to change them to CHAR, so you might want to add something like 'and f.rdb$field_type = 14' or similar to the WHERE clause) and am uncertain which other things might be affected (could the fields be primary or foreign keys, are they indexed or used in stored procedures/triggers etc, and does that matter with such an update or not?).