Find if any columns that contain null in SQL Server

Question

I new to SQL, I am having some difficulty solving this problem.

I want to write the select query which returns all those rows which have the null value in it.

I have more than 50 columns on the table and can be added some extra column it and because of that I getting difficult to write where condition.

AFAIK we can use is null but I don't want to repeat it for that much columns.

Please help me solve it. Let me know if any additional information required.

Answer 1

You can try this dynamic SQL query. This query will return row if any column contain null value.

DECLARE @tb NVARCHAR(255) = N'dbo.[tablename]';

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

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

EXEC sp_executesql @sql;