HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMajor

Test if any columns are NULL

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
columnsarenullanytest

Problem

I'm trying to figure out an easy query I can do to test if a large table has a list of entries that has at least ONE blank (NULL / empty) value in ANY column.

I need something like

SELECT * FROM table AS t WHERE ANY(t.* IS NULL)


I don't want to have to do

SELECT * FROM table AS t WHERE t.c1 = NULL OR t.c2 = NULL OR t.c3 = NULL


This would be a HUGE query.

Solution

An extension to @db2's answer with less (read:zero) hand-wrangling:

DECLARE @tb nvarchar(512) = N'dbo.[table]';

DECLARE @sql nvarchar(max) = N'SELECT * FROM ' + @tb
    + N' WHERE 1 = 0';

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

EXEC sys.sp_executesql @sql;

Code Snippets

DECLARE @tb nvarchar(512) = N'dbo.[table]';

DECLARE @sql nvarchar(max) = N'SELECT * FROM ' + @tb
    + N' WHERE 1 = 0';

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

EXEC sys.sp_executesql @sql;

Context

StackExchange Database Administrators Q#14864, answer score: 20

Revisions (0)

No revisions yet.