How to convert newlines (replace \r\n with \n) across all varchar and nvarchar fields in a database

Question

I am recovering from a bug in a system I built where I did not take into account that IE generates Windows-style newlines (\r\n) and other browsers generate Unix-style newlines (\n) when posting HTML forms with text areas. Now I need to convert all Windows-style newlines (\r\n) to Unix-style newlines (\n) throughout the varchar and nvarchar fields in my SQL-Server database.

Is there a way to iterate through all tables/rows in T-SQL and replace instances of '\r\n' with '\n' for varchar and nvarchar fields?

EDIT: I think the replace part would be something like

REPLACE(@fieldContents, CHAR(13)+CHAR(10), CHAR(10))

The hard part is doing this across all varchar and nvarchar fields.

Answer 1

Something like this? You could then dynamically execute these strings or just cut/paste the results and execute them in a query window.

select 'update ' + sc.name + '.' + t.name + ' set ' + c.name + ' = replace(' + c.name + ', CHAR(13)+CHAR(10), 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