Loop throuh a result set and set any Null Values

• andre-394971

  SSChasing Mays

  Points: 659

  More actions

  January 5, 2012 at 9:44 pm

  #391251

  Hello There,

  Im using the following to retreive all the tables and columns from a database

  SELECT s.name AS SchemaName, t.name AS TableName, c.name AS ColumnName, sys.types.name AS DataType

  FROM sys.columns AS c INNER JOIN

  sys.tables AS t ON c.object_id = t.object_id INNER JOIN

  sys.schemas AS s ON t.schema_id = s.schema_id INNER JOIN

  sys.types ON c.system_type_id = sys.types.system_type_id

  WHERE (c.is_identity = 0)

  ORDER BY TableName

  I then want to be able to LOOP through the result set and for every column do an update on the actual Table and columns in that table

  Something like.....

  Update {TableName}

  Set {ColumnName} =

  Case DataType 'Int' then 0 else Null end,

  Case DataType 'varchar' then '' else Null end,

  Case DataType 'tinyInt' then 0 else Null end.......

  Where {ColumnName} is NULL

  Im trying to find a global way to update all database tables in a database and update any records that have a null value in a column.

  I can do this in a .net application but was hoping there is a way to do it using SQL script.

  Many thanks

  SQL 2005/2008/2008R2

• sqlgeek

  SSC-Addicted

  Points: 480

  More actions

  January 5, 2012 at 11:02 pm

  #1429455

  Hi,

  I hope this could help you..

• DECLARE @SchName VARCHAR(100)

  DECLARE @TabName VARCHAR(500)

  DECLARE @ColName VARCHAR(500)

  DECLARE @DataType VARCHAR(100)

  DECLARE @DySQL NVARCHAR(MAX)

  DECLARE CUR_UPDATE_TABLE CURSOR FAST_FORWARD FOR

  SELECT s.name,t.name,c.name, sys.types.name FROM sys.columns AS c

  INNER JOIN sys.tables AS t ON c.object_id = t.object_id

  INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id

  INNER JOIN sys.types ON c.system_type_id = sys.types.system_type_id

  WHERE (c.is_identity = 0)

  OPEN CUR_UPDATE_TABLE

  FETCH NEXT FROM CUR_UPDATE_TABLE INTO @SchName,@TabName,@ColName,@DataType

  WHILE @@FETCH_STATUS =0

  BEGIN

  SET @DySQL=''

  IF (@DataType='int' OR @DataType='tinyint')

  BEGIN

  SET @DySQL='UPDATE '+@SchName+'.'+@TabName+' SET '+@ColName+'=0 WHERE '+@ColName+' IS NULL'

  END

  ELSE IF @DataType='varchar'

  BEGIN

  SET @DySQL='UPDATE '+@SchName+'.'+@TabName+' SET '+@ColName+'='''' WHERE '+@ColName+' IS NULL'

  END

  EXEC (@DySQL)

  FETCH NEXT FROM CUR_UPDATE_TABLE INTO @SchName,@TabName,@ColName,@DataType

  END

  CLOSE CUR_UPDATE_TABLE

  DEALLOCATE CUR_UPDATE_TABLE