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
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;
Approach:
