Archive for April, 2016


In order to convert newlines (replace \r\n with \n) across all varchar and nvarchar fields in a database below is the simple query that will be helpful to generate queries to update the data in table.


select 'update ' + sc.name + '.' + t.name + ' set ' + c.name + ' = REPLACE(REPLACE(' + c.name + ', CHAR(13), ''''), CHAR(10), '')'
from sys.columns c
inner join sys.systypes st
on c.system_type_id = st.xtype
and CHARINDEX('varchar', st.name) <> 0
inner join sys.tables t
on c.object_id = t.object_id
inner join sys.schemas sc
on t.schema_id = sc.schema_id