SQL Server: Set all null values to blank

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; 

Leave a comment

Please note that we won't show your email to others, or use it for sending unwanted emails. We will only use it to render your Gravatar image and to validate you as a real person.