-1

I've a table with 100+ (nullable) columns and 10,000+ rows. I'd like to fetch rows that have no columns null.

id|f001|f002|f003|....f100
1 |null|aa  | ss |sada
2 |sdad|aa  | ss |sada
3 |as  |aa  | ss |sada
4 |null|null| ss |sada

In above case, only rows with ID 2 and 3 should be the output.

I cannot use cursor nor can I specify the column names individually.

PK is of course ID. It is legacy code and almost the entire application depends on it - can't do anything about the table design.

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)
Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows Server 2016 Datacenter 6.3 <X64> 
(Build 14393: ) (Hypervisor)
  • I have marked this as a duplicate for the time being because the question only says what you cannot do, not what solution elements would be acceptable. The linked Q & A contains examples of most standard solutions to this problem including dynamic SQL and XML methods. Adapting "any column is null" to "no column is null" is usually straightforward. – Paul White Oct 14 at 9:41
1

One possible approach is to build dynamically a statement, which (based on the data in the question) is like the following sample code:

SELECT id 
FROM YourTable 
WHERE NOT EXISTS (
   SELECT v.f 
   FROM (VALUES 
     (CASE WHEN [f001] IS NOT NULL THEN 1 END), 
     (CASE WHEN [f002] IS NOT NULL THEN 1 END), 
     (CASE WHEN [f003] IS NOT NULL THEN 1 END), 
     (CASE WHEN [f100] IS NOT NULL THEN 1 END)
   ) v (f) 
   WHERE v.f IS NULL
)

The whole statement uses the information from the system catalaog views:

-- Dynamic statement
DECLARE @stm nvarchar(max)

-- All columns, except "id"
SELECT @stm = (
    SELECT CONCAT(N', (CASE WHEN ', QUOTENAME(col.[name]), N' IS NOT NULL THEN 1 END)')
    FROM sys.columns col
    JOIN sys.tables tab ON col.object_id = tab.object_id
    JOIN sys.schemas sch ON tab.schema_id = sch.schema_id
    WHERE (tab.[name] = N'YourTable') AND (sch.[name] = N'dbo') AND (col.[name] <> N'id')
    FOR XML PATH('')
)

-- Final statement
SELECT @stm = CONCAT(
    N'SELECT id FROM YourTable WHERE NOT EXISTS (SELECT v.f FROM (VALUES ',
    STUFF(@stm, 1, 2, N''),
    N') v (f) WHERE v.f IS NULL)'
)

-- Print and execute the statement
PRINT @stm
DECLARE @err int
EXEC @err = sp_executesql @stm
IF @err <> 0 PRINT 'Error'
| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.