SQL: List Rows with at least One NULL Value

During data audits, migrations, or quality checks, it’s often necessary to identify records that contain at least one NULL value.
When a table has many columns, manually writing OR column IS NULL quickly becomes inefficient and error-prone.

A better approach is to use dynamic SQL driven by table metadata.

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = 'SELECT * FROM MyTableName WHERE ' +
    STRING_AGG(QUOTENAME(name) + ' IS NULL', ' OR ')
FROM sys.columns
WHERE object_id = OBJECT_ID('MyTableName');

EXEC sp_executesql @sql;

How It Works

• sys.columns retrieves all column names for the target table
• STRING_AGG builds a single WHERE clause using OR
• QUOTENAME safely handles special characters in column names
• sp_executesql executes the dynamically generated query

Practical Use Cases

• Data validation after Excel or CSV imports
• Identifying incomplete records
• Data cleansing before reporting or migration

Important Notes

• Best suited for analysis and troubleshooting, not frequent production execution
• On large tables, consider adding filters or using EXISTS to improve performance

A simple but powerful technique for quickly exposing hidden data quality issues in SQL Server.