Generate Update Script for a Table



  select 'UPDATE yourtable SET '+ STUFF  (( select ',' + quotename(name) + ' = isnull(' + quotename(name) + ', 0)'
               FROM   sys.columns
WHERE  object_id = object_id('yourtable')
  AND  type_name(system_type_id) LIKE '%int'
               FOR XML PATH('')), 1, 1, '') AS yourquery




-- Or use a cursor solution
DECLARE @TABLE_NAME  NVARCHAR(50) = 'AMaster',
        @TABLE_SCHEMA NVARCHAR(50) = 'dbo',
        @sql          NVARCHAR(4000),
        @col          NVARCHAR(50)
 
DECLARE c_cursor CURSOR FOR
  SELECT column_Name
  FROM   [INFORMATION_SCHEMA].[COLUMNS]
  WHERE  TABLE_NAME = @TABLE_NAME
         AND TABLE_SCHEMA = @TABLE_SCHEMA
         AND IS_Nullable = 'YES'

OPEN c_cursor;
FETCH NEXT FROM c_cursor INTO @col;
WHILE ( @@FETCH_STATUS = 0 )
  BEGIN
      SET @sql = N' UPDATE  ' + Quotename(@TABLE_SCHEMA) + '.'  + Quotename( @TABLE_NAME) +' SET ' + @col +' = 0 WHERE ' + @col + ' Is Null'        
                
      print @sql
    --  EXEC (@sql);
      FETCH NEXT FROM c_cursor INTO @col; 
  END
CLOSE c_cursor;
DEALLOCATE c_cursor;


 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s