I think if you try to use COALESCE instead of COLLATE , since COALESCE(att1, att2, att3) return the first value that is not null, your code will return all rows where everything is NULL .

You can try to return every rows where there's a NULL value :

SELECT * FROM TABLE_NAME
WHERE
    col1 IS NULL
    OR col2 IS NULL
    OR ...

or for every column, you count the non-null values :

SELECT COUNT(*), COUNT(col1), COUNT(col2), ...
FROM TABLE_NAME

Every COUNT(colX) with a different value than COUNT(*) contains NULL values

Use this to figure out which columns have at least one null value:

DECLARE @col_name VARCHAR(50)
DECLARE @table_name VARCHAR(50) 
DECLARE @schema_name VARCHAR(50)
DECLARE @sql VARCHAR(500)

SET @table_name ='yourtable'
SET @schema_name ='yourschema'

DECLARE columns CURSOR FOR
  select COLUMN_NAME from INFORMATION_SCHEMA.columns
  where TABLE_NAME =@table_name and TABLE_SCHEMA =@schema_name

OPEN columns  
FETCH NEXT FROM columns INTO @col_name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    set @sql = 'if exists(SELECT * from '+@schema_name+'.'+@table_name+' where '+@col_name+' is null) select '''+@col_name+''' '
    exec (@sql) 
    

FETCH NEXT FROM columns INTO @col_name  
END 

CLOSE columns  
DEALLOCATE columns

Here is a generic way to determine columns that have NULL values in all rows in a table.

SQL

USE tempdb;
GO

DROP TABLE IF EXISTS #tmpTable;

CREATE TABLE #tmpTable (
     client_id int,
     client_name varchar(500),
     client_surname varchar(500),
     city varchar(500),
     state varchar(500));
    
INSERT #tmpTable VALUES
(1,'Miriam',NULL,'Las Vegas',NULL),
(2,'Astrid',NULL,'Chicago',NULL),
(3,'David',NULL,'Phoenix',NULL),
(4,'Hiroki',NULL,'Orlando',NULL);

SELECT name 
FROM tempdb.sys.columns
WHERE object_id = object_id('tempdb..#tmpTable')
    AND is_nullable = 1
EXCEPT
SELECT x.value('local-name(.)', 'SYSNAME')
FROM #tmpTable AS t
    CROSS APPLY (SELECT t.* FOR XML PATH(''), TYPE, ROOT('root')) AS t1(c)
    CROSS APPLY c.nodes('/root/*') AS t2(x);

Output