SQL Server - Set all null values to blank

Posted by Blake on 8/13/2015

Here is a simple script that will generate update statements that will set all nullable fields in a database to a default value. In this case, I'm only looking at varchar/nvarchar types and I'm setting them to blank. A better script would update a table at a time instead of a field at a time but as Homer Simpson once said, there's the right way, the wrong way and my way... what's the wrong way?... my way, only faster.

Once I do it the right way, I'll repost this blog entry, just don't have time at this moment. ;-)


SELECT 'UPDATE ' + t.name + ' SET ' + c.name + ' = '''' WHERE ' + c.name + ' IS NULL;' AS [SQL]
    FROM sys.tables AS t
    INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
    INNER JOIN sys.types AS ty ON c.user_type_id=ty.user_type_id
    WHERE c.is_nullable = 1 AND ty.name LIKE '%varchar%'
    ORDER BY t.name;