SQL count empty cells in unknown number of columns

Question

I have a Table with an unknown number of columns (and unknown column names too) with different types of data (can be anything from bit to nvarchar or datetime) like the following:

ID   | Col2 | Col3 | Customer |  ..(etc)..
1    | NULL |  0   |  CustA   |    
2    | valA |  1   |  NULL    |
3    | valB | NULL |  (empty) |

I need a query which counts all NULL and empty cells on every column and outputs the result like this:

Column_Name  |  No_Of_Empty_And_Null_Cells | 
   Col2      |             1               |
   Col3      |             1               |
 Customer    |             2               |
 (etc...)    |                             |

I understand that I have to use dynamic queries and UNPIVOT, but my SQL knowledge is nowhere near that!

How to count all NULL values in a table? does not seem to work as it is MySQL related, not MS SQL

Answer 1

You might try this dynamic SQL code out:

DECLARE @schema VARCHAR(100)='dbo';
DECLARE @tableName VARCHAR(100)='SomeTable';

DECLARE @DynamicSelect VARCHAR(MAX)=
(
  STUFF((SELECT 'UNION SELECT ''' + COLUMN_NAME + ''' AS COLUMN_NAME' + 
         ', (SELECT COUNT(*) FROM ' + @schema + '.' + @tableName + ' WHERE [' + COLUMN_NAME + '] IS NULL) AS No_Of_Null_Cells '
         FROM INFORMATION_SCHEMA.COLUMNS AS c
         WHERE c.TABLE_SCHEMA=@schema AND c.TABLE_NAME=@tableName AND c.IS_NULLABLE='YES'
         FOR XML PATH('')
        ),1,6,'')
);
exec (@DynamicSelect)


DECLARE @DynamicSelect2 VARCHAR(MAX)=
(
  STUFF((SELECT 'UNION SELECT ''' + COLUMN_NAME + ''' AS COLUMN_NAME' + 
         ', (SELECT COUNT(*) FROM ' + @schema + '.' + @tableName + ' WHERE LTRIM(RTRIM([' + COLUMN_NAME + '])) ='''') AS No_Of_Empty_Cells '
         FROM INFORMATION_SCHEMA.COLUMNS AS c
         WHERE c.TABLE_SCHEMA=@schema AND c.TABLE_NAME=@tableName AND c.DATA_TYPE LIKE '%char%'
         FOR XML PATH('')
        ),1,6,'')
);
exec (@DynamicSelect2)

Answer 2

Here's a method to return the fields with null or empty values from a table.
Via generating and executing a dynamic query with an unpivot.
Tested on a SQL Server 2014.

DECLARE @SchemaName SYSNAME = 'YourDatabase';
DECLARE @TableName SYSNAME = 'YourTable';

DECLARE @SQL NVARCHAR(MAX);

WITH COL AS (
    SELECT c.name
    FROM sys.objects o
    INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
    INNER JOIN sys.columns c ON o.[object_id] = c.[object_id]
    WHERE o.[type] = 'U'
      AND s.name = @SchemaName 
      AND o.name = @TableName
      AND c.is_nullable = 1
)
SELECT @SQL = 'SELECT up.column_name, up.total_empty 
FROM (
 SELECT ' + CHAR(13) + STUFF((
    SELECT CHAR(13) + ', ' + QUOTENAME(name) + ' = SUM(IIF(LEN(RTRIM(' + QUOTENAME(name) + '))>0,0,1))'
    FROM COL
    FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, ' ') + '
 FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ') q
 UNPIVOT
 (total_empty for column_name in ('+
  STUFF((
    SELECT CHAR(13) + ', ' + QUOTENAME(name) FROM COL
    FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, ' ')
   +')
) up where up.total_empty > 0 order by up.column_name';

--SELECT @SQL;
EXEC sys.sp_executesql @SQL;