Adjusting Table Structure using SQL

I was asked by a partner how he could adjust his table structure. He needed to check if a char field is available and adjust its size to a minimum of 40 characters. So I came up with this script:

declare @tbname string;  
declare @fieldname string;  
declare @fieldsize integer;  
declare @col cursor as select * from system.columns where name like @fieldname and parent like @tbname;  
declare @sql string;  

@tbname='mytable';  
@fieldname='myfield';  
@fieldsize=40;  

open @col;  
if fetch @col then  
  --column exists, check size  
  if @col.field_length<@fieldsize then  
    @sql='alter table ['+@tbname+'] alter column ['+@fieldname+'] '+  
         '['+@fieldname+'] CHAR('+cast(@fieldsize as sql_char)+')';  
  endif;  
else  
  --column does not exist  
  @sql='alter table ['+@tbname+'] add column '+  
       '['+@fieldname+'] CHAR('+cast(@fieldsize as sql_char)+')';  
endif;  
close @col;   
if @sql is not null then  
  execute immediate @sql;  
endif;  

If you want to be more flexible, initialize the variables with parameters (Delphi example):

//...  
query.sql.Add('@tbname=:tbname;');  
query.sql.Add('@fieldname=:fieldname;');  
query.sql.Add('@fieldsize=:fieldsize;');  
//...  

If you want to use it in multiple tables or for multiple fields, simply put it into a User Defined Function (UDF):

Create Function AdjustCharField(tablename string, fieldname string, fieldsize integer)  
returns logical  
begin  
declare @tbname string;  
declare @fieldname string;  
declare @fieldsize integer;  
declare @col cursor as select * from system.columns where name like @fieldname and parent like @tbname;  
declare @sql string;  

@tbname=tablename;  
@fieldname=fieldname;  
@fieldsize=fieldsize;  

open @col;  
if fetch @col then  
  --column exists, check size  
  if @col.field_length<@fieldsize then  
    @sql='alter table ['+@tbname+'] alter column ['+@fieldname+'] '+  
         '['+@fieldname+'] CHAR('+cast(@fieldsize as sql_char)+')';  
  endif;  
else  
  --column does not exist  
  @sql='alter table ['+@tbname+'] add column '+  
       '['+@fieldname+'] CHAR('+cast(@fieldsize as sql_char)+')';  
endif;  
close @col;   
try  
  if @sql is not null then  
    execute immediate @sql;  
  endif;  
  return true;  
catch all  
  return false;  
endtry;  
end;  

Now you can use this function for example in a script:

if AdjustCharField('mytable','myfield',40) then  
  select 'success' from system.iota;  
else  
  select 'failed' from system.iota;  
endif;