T-SQL Script to update string NULL with default NULL

Problem :

It is common to have nullable columns in the table,but if we populate those nullable columns with string NULL instead of default NULL,there araises the problem.

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 belongs to the passed 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 ,SQL Server 2012.