drop table dbo.temp_tabinfo;
select t1.name tname,t2.name cname,t3.name ctname,t2.max_length ctlen,0 maxlen,0 ispk into dbo.temp_tabinfo
from sys.tables t1,sys.columns t2,sys.types t3
where t1.object_id=t2.object_id and charindex('DYNA_',t1.name)>0 and charindex('FM_',t2.name)=0
and t2.system_type_id =t3.system_type_id and (t3.name='varchar' or t3.name='text')
order by t1.name,t2.name;
alter table temp_tabinfo alter column maxlen bigint null;
delete from temp_tabinfo where (tname='DYNA_NR_ContPageClass' and cname='name')
or (tname='DYNA_WL_Company' and cname='randcode');
select 'update temp_tabinfo set maxlen=(select max(len(isnull('+cname+',''''))) from '+tname+') where tname='''+tname+''' and cname='''+cname+''';' sql
from temp_tabinfo
where ctname='varchar'
union
select 'update temp_tabinfo set maxlen=0 where ctname=''varchar'' and maxlen is null;';
---------
--select 'alter table '+tname+' alter column '+cname+' nvarchar(500);' sql
--from temp_tabinfo
--where maxlen<250 and ctlen<=500 and ctname='varchar'
--union
--select 'alter table '+tname+' alter column '+cname+' nvarchar(4000);' sql
--from temp_tabinfo
--where ((maxlen>=250 and maxlen<2000) or ctlen>500) and ctname='varchar'
