espressionist espressionist - 3 months ago 12
SQL Question

SQL count empty cells in unknown number of columns

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

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)