Problem


It is common to have nullable columns in a table but if we populate those nullable columns with string NULL instead of default NULL then a problem arises.

Effects of Problem:

If we populate nullable columns with string column we cannot make use of NULL functions available in SQL Server.

For Example:

USE [AdventureWorks2012]
GO
--Create test table with two columns to hold string & default NULL
CREATE TABLE Test_Null(Id INT IDENTITY(1,1),StringNull VARCHAR(10) ,DefaultNull VARCHAR(10))
INSERT Test_Null (StringNull) SELECT  'NULL'
INSERT Test_Null  SELECT  'NULL',NULL
  
--Execute below two queries to find how "IS NULL" works with string & default NULL
SELECT * FROM Test_Null WHERE StringNULL IS NULL
SELECT * FROM Test_Null WHERE DefaultNull IS NULL
  
--Execute below two queries to find how "ISNULL" works with string & default NULL
SELECT ISNULL(StringNULL,0) StringNULL FROM Test_Null 
SELECT ISNULL(DefaultNull,0) DefaultNull FROM Test_Null

Solution


USE [AdventureWorks2012]
GO
SET NOCOUNT ON
DECLARE @query NVARCHAR(MAX),
@table_count INT,
@column_count INT,
@tablename VARCHAR(100),
@Columnname VARCHAR(100),
@Schemaname VARCHAR(100) = 'HumanResources', --schema names to be passed 
@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) = @Schemaname
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) = @Schemaname 
AND OBJECT_NAME(T.OBJECT_ID) = @tablename AND T.type = 'U'
AND C.is_nullable = 1 
AND TY.system_type_id IN (167,175,231,239) --only character columns
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 ['+@Schemaname+'].['+@tablename+'] SET ['+@Columnname+'] = NULL WHERE ['+@Columnname +'] = ''NULL''' + CHAR(10) + 'GO'
SET @j = @j + 1
PRINT @query
--To execute the generated Update scripts
--EXEC (@query)
END
SET @i = @i + 1
END

Note:
i) Above code will generate UPDATE scripts for tables that belong to the passed in schema names to the variable @Schemaname.
ii) Above code will generate UPDATE scripts only for character columns (VARCHAR, CHAR, NVARCHAR).
iii) Code is tested and working with SQL Server 2008 and SQL Server 2012.


See Also



Others Languages