General purpose TSQL null remover

Why do you want to do this? What problem are you hoping to solve?

Here you go...

SELECT
   *
INTO
   #TABS_COLS
FROM
   information_schema.columns
ORDER BY
   table_name, ordinal_position
GO

SELECT
   SEQ_NO = IDENTITY(INT),
   DISTINCT TABLE_NAME
INTO
   #TABS
FROM
   #TABS_COLS
GO

DECLARE
   @MIN INT,
   @MAX INT,
   @TABLE_NAME VARCHAR(100),
   @QRY VARCHAR(8000)

CREATE TABLE #DUMMY(NUM INT)

SELECT @MAX = MAX(SEQ_NO) FROM #TABS
SELECT @MIN = 1

WHILE(@MIN <= @MAX)
BEGIN

   SELECT @TABLE_NAME = '',@QRY=''
   
   SELECT @TABLE_NAME = TABLE_NAME FROM #TABS WHERE SEQ_NO = @MIN
   
   SELECT @QRY = 'UPDATE '+ @TABLE_NAME + 'SET'
   
   UPDATE #TABS_COLS SET @QRY = @QRY + 'ISNULL('+ COLUMN_NAME + ',SPACE(1)),'
   SELECT @QRY = @QRY + 'WHERE TABLE_NAME = ' + @TABLE_NAME
   
   SELECT @QRY
   
   SELECT @MIN = @MIN + 1

END
GO

Select allOpen in new window

Missed to include text column types. pls include

Make sure you are sure you want to do this; in database NULL has a specific meaning and purpose. If your reports are coming out with some text substitute like * or <NULL> and you want them to look prettier, this should be a problem for the report generator to convert.

/*
Below is code to do the changes.  This code is different from earlier code as follows:

1) uses only one UPDATE per table, with all columns (rather than a separate table pass for every column);
2) table names/name patterns can be excluded from processing;
3) data type names/name patterns can be excluded from processing;
4) data types that require a replacement value of '0' (because '' is invalid) are identified and set accordingly;
5) the code can just (A) PRINT the commands only or (B) PRINT and EXEC the commands.

The code is currently set to PRINT the commands only, not EXEC them.  To also exec the commands as they are generated, specify:
SET @exec_sql = 1
rather than 0.
*/


SET NOCOUNT ON

IF OBJECT_ID('tempdb.dbo.#tables_to_exclude') IS NOT NULL
    DROP TABLE #tables_to_exclude
IF OBJECT_ID('tempdb.dbo.#data_types_to_exclude') IS NOT NULL
    DROP TABLE #data_types_to_exclude
IF OBJECT_ID('tempdb.dbo.#replacement_values') IS NOT NULL
    DROP TABLE #replacement_values

DECLARE @print_sql bit
DECLARE @exec_sql bit

CREATE TABLE #tables_to_exclude (
    table_name_pattern sysname --can be individual name or LIKE pattern
    )
CREATE TABLE #data_types_to_exclude (
    data_type_pattern sysname --can be individual name or LIKE pattern
    )

INSERT INTO #tables_to_exclude SELECT 'sys%'
INSERT INTO #tables_to_exclude SELECT 'tmp%'
INSERT INTO #tables_to_exclude SELECT 'temp%'
--INSERT INTO #tables_to_exclude VALUES('%work%')

INSERT INTO #data_types_to_exclude SELECT 'geography'
INSERT INTO #data_types_to_exclude SELECT 'geometry'
INSERT INTO #data_types_to_exclude SELECT 'hierarchyid'
INSERT INTO #data_types_to_exclude SELECT 'sql_variant'
--INSERT INTO #data_types_to_exclude SELECT 'timestamp'
INSERT INTO #data_types_to_exclude SELECT 'uniqueidentifier'
--INSERT INTO #data_types_to_exclude SELECT 'xml'

--INSERT INTO #data_types_to_exclude SELECT '%date%'
--INSERT INTO #data_types_to_exclude SELECT '%time%'

SET @print_sql = 1
SET @exec_sql = 0

------------------------------------------------------------------------------------------------------------------------

CREATE TABLE #replacement_values (
    ident int IDENTITY(1, 1) NOT NULL,
    data_type sysname NOT NULL,
    replacement_value varchar(1) NOT NULL
    )
INSERT INTO #replacement_values
SELECT '%dec%', '0' UNION ALL
SELECT 'float', '0' UNION ALL
SELECT '%numeric%', '0'

------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID('tempdb..#find_results') IS NOT NULL
    DROP TABLE #find_results
IF OBJECT_ID('tempdb..#tables_to_process') IS NOT NULL
    DROP TABLE #tables_to_process

CREATE TABLE #tables_to_process (
    schema_id int NOT NULL,
    object_id int NOT NULL,
    schema_name nvarchar(128) NOT NULL,
    table_name nvarchar(128) NOT NULL,
    column_sql_update nvarchar(max) NULL,
    PRIMARY KEY ( schema_name, table_name )
    )

DECLARE @schema_id int
DECLARE @object_id int
DECLARE @schema_name sysname
DECLARE @table_name sysname
DECLARE @column_sql_update nvarchar(max)
DECLARE @sql_template_column_set nvarchar(max)
DECLARE @sql nvarchar(max)

SET @sql_template_column_set = ',[$column$] = CASE WHEN [$column$] IS NULL THEN ''$value$'' ELSE [$column$] END'

INSERT INTO #tables_to_process ( schema_id, object_id, schema_name, table_name )
SELECT
    t.schema_id, t.object_id, SCHEMA_NAME(t.schema_id), OBJECT_NAME(t.object_id)
FROM sys.tables t
WHERE
    t.is_ms_shipped = 0 AND
    NOT EXISTS (
        SELECT 1
        FROM #tables_to_exclude tte
        WHERE
            t.name LIKE tte.table_name_pattern
    )                

--SELECT * FROM #tables_to_process

DECLARE csrTable CURSOR FAST_FORWARD FOR
SELECT schema_id, object_id, schema_name, table_name
FROM #tables_to_process
ORDER BY schema_name, table_name

OPEN csrTable

SET NOCOUNT ON

WHILE 1 = 1
BEGIN
    FETCH NEXT FROM csrTable INTO @schema_id, @object_id, @schema_name, @table_name
    IF @@FETCH_STATUS <> 0
        BREAK    
    SET @column_sql_update = ''
    /*
    SELECT @column_sql_update = @column_sql_update +
        REPLACE(REPLACE(@sql_template_column_set,
        '$column$', c.name),
        '$value$', ISNULL((SELECT rv.replacement_value
            FROM #replacement_values rv WHERE ty.name LIKE rv.data_type), ''))
    FROM sys.columns c
    INNER JOIN sys.types ty ON
        c.system_type_id = ty.system_type_id
    WHERE
        c.object_id = @object_id AND
        NOT EXISTS (
            SELECT 1
            FROM #data_types_to_exclude dtti
            WHERE
                ty.name LIKE dtti.data_type_pattern
        )
    ORDER BY
        c.name
    */
    SELECT @column_sql_update = CAST((
        SELECT CAST('' AS nvarchar(max)) +
            REPLACE(REPLACE(@sql_template_column_set,
            '$column$', c.name),
            '$value$', ISNULL((SELECT rv.replacement_value
                FROM #replacement_values rv WHERE ty.name LIKE rv.data_type), ''))
        FROM sys.columns c
        INNER JOIN sys.types ty ON
            c.system_type_id = ty.system_type_id
        WHERE
            c.is_nullable = 1 AND
            c.is_computed = 0 AND
            c.object_id = @object_id AND
            NOT EXISTS (
                SELECT 1
                FROM #data_types_to_exclude dtti
                WHERE
                    ty.name LIKE dtti.data_type_pattern
            )
        ORDER BY
            c.name
        FOR XML PATH('')
        ) AS nvarchar(max))
    --*/
    SET @sql = @column_sql_update
    SET @sql =
        'UPDATE [' + @schema_name + '].' +
            '[' + @table_name + '] ' + CHAR(13) + CHAR(10) +
        'SET ' + SUBSTRING(@sql, 2, LEN(@sql)) + CHAR(13) + CHAR(10) +
        'FROM [' + @schema_name + '].[' + @table_name + '] ' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
    IF @print_sql > 0 OR @exec_sql = 0
        PRINT @sql --AS [--sql]
    IF @exec_sql > 0
        EXEC(@sql)
END --WHILE

DEALLOCATE csrTable

SET NOCOUNT OFF

Not only is the recommended solution effective, concise, and helpfully commented, it was quickly posted.