this generates the script to alter the columns and remove the user defined data types

updatecolumns.sql

/*
This is very rough, it doesn't work for ints/bits/etc only varchars

This does not work if there are schema bound views

*/
select 'alter table ' + s.name + '.' + object_name(c.object_id) + ' ALTER COLUMN ' + c.name + ' ' + ttt.name + '(' + convert(varchar,c.max_length) + ');',
c.max_length , c.*, tt.*, s.* from sys.columns  c
inner join sys.tables tt on tt.object_id = c.object_id 
inner join sys.schemas s on s.schema_id = tt.schema_id
inner join sys.types ttt on ttt.system_type_id = ttt.user_type_id and c.system_type_id= ttt.system_type_id
where c.user_type_id != c.system_type_id