CODEWITHSUNDEEP
sql-server-2008select
Y2theZ
Y2theZ

Reputation: 10412

select rows that have no null values

in an sql database I need a script that will select all the rows that has no null value something like this:

For all the rows in the database
     if row has no null values
         select it

Columns are dynamic, I cannot know their number or names

Thanks you

Upvotes: 1

Views: 4746

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

This is the opposite of this question Test if any fields are NULL.

The answer by Martin Smith modified to find rows without null values would look like this.

;with xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' as ns)
select *
from YourTable as T
where
  (
    select T.*
    for xml path('row'), elements xsinil, type 
  ).exist('//*/@ns:nil') = 0

And the answer provided by Aaron Bertrand modified would be...

DECLARE @tb NVARCHAR(255) = N'YourTable';

DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM ' + @tb
    + ' WHERE 1 = 1';

SELECT @sql += N' AND ' + QUOTENAME(name) + ' IS NOT NULL'
    FROM sys.columns 
    WHERE [object_id] = OBJECT_ID(@tb);

EXEC sp_executesql @sql;

Upvotes: 4

Kash
Kash

Reputation: 9039

Approach:

  • Get column names of a table from sys.columns
  • do the select with multiple WHERE conditions ANDed: WHERE ColA IS NOT NULL AND ColB IS NOT NULL and so on...

Upvotes: -1

Related Questions