T-SQL Script to update string NULL with default NULL
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.