How to remove every empty row from any SQL table

Question

I have a table with over 130 columns in it (don't ask--I didn't make it). We need to remove every empty row (each field is empty) from this table without explicitly listing each column. Ideally, I would like a solution that uses dynamic SQL and could be applied to any table. How can I do this?

Answer 1

This will get you part way there at least:

DECLARE @myTable VARCHAR(MAX)

SET @MyTable = 'myTable'
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL + ' AND ','')+  '(' +quotename(COLUMN_NAME) + ' = '''' OR  ' +quotename(COLUMN_NAME) + ' IS NULL)'
FROM information_schema.columns 
WHERE table_NAME = @myTable

SET @SQL = 'DELETE FROM ' + quotename(@myTable) + ' WHERE ' + @sql
select @sql 

Note the use of QUOTENAME to handle oddly named columns. Also note that this assumes all columns are string columns (or can be implicitly converted to a string). In your solution, you may want some conditional logic (using CASE) to handle different datatypes e.g.

SELECT @SQL = COALESCE(@SQL + ' AND ','')+  '(' +quotename(COLUMN_NAME) + ' = ' + CASE WHEN data_type = 'int' then '0' when data_type = 'varchar' then '''' else '''' end + ' OR  ' +quotename(COLUMN_NAME) + ' IS NULL)'
    FROM information_schema.columns 
    WHERE table_NAME = @myTable

Answer 2

This will remove the rows where every column is null.

-- Sample table
declare @T table
(
  Col1 int,
  Col2 datetime,
  Col3 bit,
  Col4 nvarchar(max)
)

-- Add sample data
insert into @T values
(null,      null, null, null),
(   1,      null, null, null),
(null, getdate(), null, null),
(null,      null,    1, null),
(null,      null, null, '')

-- Delete rows where all column values are null   
;with C(XmlCol) as
(
  select
    (select T.*
     for xml path('row'), type)
  from @T as T
)
delete from C
where C.XmlCol.exist('row/*') = 0

Result:

Col1        Col2                    Col3  Col4
----------- ----------------------- ----- ----------
1           NULL                    NULL  NULL
NULL        2011-11-23 14:09:42.770 NULL  NULL
NULL        NULL                    1     NULL
NULL        NULL                    NULL  

https://data.stackexchange.com/stackoverflow/q/118893/

Edit:

If you want to remove empty string fields as well as null it would look like this:

;with C(XmlCol) as
(
  select
    (select T.*
     for xml path('row'), type)
  from @T as T
)
delete from C
where C.XmlCol.exist('row/*[. != ""]') = 0