Tuesday, July 14, 2015

Select rows where any column contains a NULL

Thanks to Aaron Bertrand
http://dba.stackexchange.com/questions/14864/test-if-any-fields-are-null

 DECLARE @table NVARCHAR(255) = N'dbo.LU_Stores';  
 DECLARE @statement NVARCHAR(MAX) = N'SELECT * FROM ' + @table + ' WHERE 1 = 0';  
 SELECT @statement += N' OR ' + QUOTENAME(name) + ' IS NULL'  
   FROM sys.columns   
   WHERE [object_id] = OBJECT_ID(@table)  
   and right(name,2) = 'ID'  
 ;  
 --print @statement  
 EXEC sp_executesql @statement;