SELECT b.name as table_name,a.name as column_name,c.name as default_name,definition as [default],is_system_named FROM syscolumns a,sys.tables b ,SYS.default_constraints c where a.id=b.object_id and a.cdefault=c.object_id
declare @sql varchar(8000) set @sql='' select @sql=@sql+ case when isnull(d.name,'') = '' then '' else 'alter table ['+a.name+'] DROP CONSTRAINT '+d.name+'; ' end +'alter table ['+a.name+'] alter column ['+b.name+'] Nvarchar('+ltrim(b.length/2)+') default '+e.text+'; ' from sysobjects a left join syscolumns b on a.id=b.id left join systypes c on b.xtype=c.xtype left join sysobjects d on d.parent_obj = a.id and d.xtype = 'D' left join syscomments e on d.id = e.id where a.xtype='u' and c.name='char'
SELECT b.name as table_name,a.name as column_name,c.name as default_name,definition as [default],is_system_named
FROM syscolumns a,sys.tables b ,SYS.default_constraints c
where a.id=b.object_id and a.cdefault=c.object_id
set @sql=''
select @sql=@sql+ case when isnull(d.name,'') = '' then '' else 'alter table ['+a.name+'] DROP CONSTRAINT '+d.name+';
' end +'alter table ['+a.name+'] alter column ['+b.name+'] Nvarchar('+ltrim(b.length/2)+') default '+e.text+';
'
from sysobjects a
left join syscolumns b on a.id=b.id
left join systypes c on b.xtype=c.xtype
left join sysobjects d on d.parent_obj = a.id and d.xtype = 'D'
left join syscomments e on d.id = e.id
where a.xtype='u' and c.name='char'