T-SQL Script to update string NULL with NULL

Below code sample will generate scripts for updating nullable columns of all tables in a database that has string NULL with NULL

USE [AdventureWorks2012]

GO

SET NOCOUNT ON

DECLARE @query NVARCHAR(MAX),
@table_count INT,
@column_count INT,
@tablename VARCHAR(100),
@Columnname VARCHAR(100),
@i INT = 1,
@j INT = 1


DECLARE @MyTableVar TABLE(Number INT IDENTITY(1,1),
Table_list VARCHAR(200));

DECLARE @MyColumnVar TABLE(Number INT IDENTITY(1,1),
Column_list VARCHAR(200));


INSERT INTO @MyTableVar
SELECT name FROM sys.tables
WHERE TYPE = 'U' AND SCHEMA_NAME(SCHEMA_ID) = 'HumanResources'

SELECT @table_count = MAX(Number) from @MyTableVar

WHILE @i <= @table_count

BEGIN

SELECT @tablename = Table_list FROM @MyTableVar WHERE Number = @i

INSERT @MyColumnVar
SELECT C.name
FROM SYS.columns C
INNER JOIN SYS.tables T ON T.object_id = C.object_id
INNER JOIN SYS.types TY ON TY.user_type_id = C.user_type_id AND TY.system_type_id = C.system_type_id
WHERE SCHEMA_NAME(T.SCHEMA_ID) = 'HumanResources' AND OBJECT_NAME(T.OBJECT_ID) = @tablename AND T.type = 'U'
AND C.is_nullable = 1
ORDER BY C.column_id

SELECT @column_count = MAX(Number) FROM @MyColumnVar

WHILE @j <= @column_count

BEGIN

SELECT @Columnname = Column_list FROM @MyColumnVar WHERE Number = @j

SET @query = 'UPDATE ['+@tablename+'] SET ['+@Columnname+'] = NULL WHERE ['+@Columnname +'] = ''NULL'''

SET @j = @j + 1

PRINT @query

--EXEC (@query)

END

SET @i = @i + 1

END
　
--Note:Schema wise above code will generate update scripts.

　
Reference :

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/31d20dd1-b940-43d8-a834-7a222f472040

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/a9ac9d4c-cfe7-47e7-86ad-fbc1db482d72