How to simplify this SQL code to find all data contain null in all row and columns?-CodePudding

I have a table which contains about 19 columns. I want to find all rows and columns that contains any null values to identify them. Below example of the result that I want.

customer_id	transaction_dt	payment_type
16789544	12/8/1678	null
16543332	null	cash
16543332	12/8/1678	null
16543332	null	online transacation

Here are the simple code that I write:

SELECT *
FROM
Customer_Details
WHERE
customer_id is null,
or transaction_dt is null, 
or payment_type is null,
.
.
.
or quantity is null,

CodePudding user response：

This query is not the most optimal but it is dynamic. Just sort of depends on what approach you want to do:

;WITH xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' AS xsi) 
SELECT * 
FROM   Customer_Details AS CD
WHERE (
    SELECT CD.* 
    FOR XML PATH('row'), ELEMENTS XSINIL, TYPE
  ).exist('/row/*/@xsi:nil') = 1

DECLARE @cd nvarchar(512) = N'dbo.[Customer_Details]';

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

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

EXEC sys.sp_executesql @sql;

CodePudding user response：

Here's some trick, if you add any columns with null values it will yield to null.

SELECT *
FROM
Customer_Details
WHERE ISNULL(customer_id   transaction_dt   payment_type, '') = ''