Delete all Null values from a table without specifying columns name manually

Question

I want to delete all the null from table but let suppose i have 200 - 500 coumns so its seems logical to delete them in a dynamic way

i was trying to it something like this while using Information_Schema.Coulmns

delete from tableName
where INFORMATION_SCHEMA.COLUMNS is null 

but as the INFORMATION_SCHEMA.COLUMNS can only be use after 'from' im confused how can i do that

Answer 1

Please try something like this (not tested).

  declare @table_name sysname = 'tableName';
  declare @sql varchar(max)
        
  select 
        @sql = coalesce(@sql + ', ', 'UPDATE ' +@table_name+' SET '+char(13))
        +[name] + '= coalesce('+[name]+', '''')' + char(13)
    from sys.columns 
  where object_id = object_id(@table_name)
  print @sql
  exec(@sql)

Answer 2

Okay i got a solution

this query is replacing each cell value to '' which is null

DECLARE @table_name NVARCHAR(100) = 'SqlInsertST2'
DECLARE @column_name NVARCHAR(100), @query NVARCHAR(MAX)

DECLARE column_cursor CURSOR FOR 
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name

OPEN column_cursor

FETCH NEXT FROM column_cursor INTO @column_name

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @query = 'UPDATE ' + @table_name + ' SET ' + QUOTENAME(@column_name) + ' = '''' WHERE ' + QUOTENAME(@column_name) + ' IS NULL'
    EXEC (@query)
    FETCH NEXT FROM column_cursor INTO @column_name
END

CLOSE column_cursor
DEALLOCATE column_cursor

Answer 3

declare @col varchar(255), @cmd varchar(max)
DECLARE getinfo cursor for
SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
WHERE t.Name = 'CUSTOMERS'

OPEN getinfo

FETCH NEXT FROM getinfo into @col

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @cmd = 'Delete FROM CUSTOMERS WHERE ' + QUOTENAME(@col) + ' IS NULL'
    exec(@cmd)

    FETCH NEXT FROM getinfo into @col
END

CLOSE getinfo
DEALLOCATE getinfo

@sagar you want to delete row which column having null value you can find delete that rows this way and here customers is demo table name