SQL Server: Update all null field as 0

I want to update some rows which be include some null fields.

How can I update these rows in SQL Server?

I am asking because a开发者_StackOverflow rows has got 180 fields. :)

Please help me.

You can use dynamic SQL to generate a script to run. The following will probably need tweaking for you to exclude columns that you don't want to update etc.

DECLARE @TableName nvarchar(500) = '[dbo].[T]'
DECLARE @DynSql nvarchar(max)

SELECT @DynSql = ISNULL(@DynSql+',','') + QUOTENAME(name) + '= ISNULL(' + QUOTENAME(name) + ',0)'
FROM sys.columns 
WHERE object_id = OBJECT_ID(@TableName)

SET @DynSql = 'UPDATE ' + @TableName + 'SET ' + @DynSql

PRINT @DynSql

--EXEC(@DynSql)

I think I understand what you are asking. This will generate an update statement for each column in your table that will set it's value to 0 if it's value is null.

declare @tableName nvarchar(100)
declare @querys varchar(max)
set @querys = ''
set @tableName = 'YOUR TABLE NAME HERE'

select @querys = @querys + 'update ' + @tableName + ' set ' +
QUOTENAME(t.[name]) + '=0 where ' + QUOTENAME(t.[name]) + ' is null;'
from (SELECT [name] FROM syscolumns
    WHERE id = (SELECT id 
        FROM sysobjects
        WHERE type = 'U'
        AND [NAME] = @tableName))t

select @querys

execute sp_executesql @sqlQuery

Well It is an old post, but I recently had this problem and wanted a dynamic SQL query using the above solution that performed the update depending on column types.

            USE [YOUR DATABASE] 
            DECLARE @tableName nvarchar(100) 
            DECLARE @name varchar(50) 
            DECLARE @dtype varchar(50) 
            DECLARE @CMD NVARCHAR (200) 

            SET @tableName = [YOUR TABLE NAME] 


            DECLARE db_cursor CURSOR FOR 

                  SELECT c.name, t.name AS Dtype 
                  FROM sys.columns c 
                  INNER JOIN sys.types t 
                  ON t.system_type_id = c.system_type_id 
                  WHERE c.[object_id] = 
                      (SELECT [object_id] FROM sys.objects WHERE type = 'U' AND [NAME] = @tableName) 

            OPEN db_cursor 
            FETCH NEXT FROM db_cursor INTO @name, @Dtype 

            WHILE @@FETCH_STATUS = 0 BEGIN

            SET @CMD = 'UPDATE ' + @tableName + ' SET ' + quotename(@name) +' = ' + 

               (CASE 
                 WHEN (@Dtype = 'bit') THEN '0' 
                 WHEN (@Dtype = 'int') THEN '0' 
                 WHEN (@Dtype = 'decimal') THEN '0' 
                WHEN (@Dtype = 'date') THEN '''1/1/1900''' 
                WHEN (@Dtype = 'datetime') THEN '''1/1/1900'''
              WHEN (@Dtype = 'uniqueidentifier') THEN '00000000-0000-0000-0000-000000000000' 
              ELSE '''''' 
              END ) 
            + ' WHERE ' + quotename(@name) +' IS NULL' 

            PRINT @CMD 

            EXEC sp_executeSQL @cmd 

            FETCH NEXT FROM db_cursor INTO @name, @Dtype 

            END 

            CLOSE db_cursor 
            DEALLOCATE db_cursor