SQL counts empty cells in an unknown number of columns

Question

I have a table with an unknown number of columns (and unknown column names too) with different data types (maybe anything: from bits to nvarchar or datetime), as shown below:

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

I need a query that counts all NULL and empty cells in each column and outputs the result as follows:

 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 knowledge of SQL is nowhere near!

How to read all null values ​​in a table? doesn't seem to work as it is related to MySQL and not to MS SQL

Answer 1

You can try this dynamic SQL code:

 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 is a way to return fields with null or empty values ​​from a table.
By creating and executing a dynamic query using univot.
Tested on 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; 

Answer 3

EDIT: this answer is for MySQL (didn't read at first)

From this answer , I think this result will count your all “null” cells:

 SET @db = 'your_database_name'; -- database SET @tb = 'your_table_name'; -- table SET @x = ''; -- will hold the column names with ASCII method applied to retrieve the number of the first char SET @numcolumns = 0; -- will hold the number of columns in the table -- figure out how many columns we have SELECT count(*) into @numcolumns FROM information_schema.columns where table_name=@tb and table_schema=@db ; -- we have to prepare some query from all columns of the table SELECT group_concat(CONCAT('ASCII(',column_name,')')) into @x from information_schema.columns where table_name=@tb and table_schema=@db ; -- after this query we have a variable separated with comma like -- ASCII(col1),ASCII(col2),ASCII(col3) -- we now generate a query to concat the columns using comma as separator (null values are omitted from concat) -- then figgure out how many times the comma is in that substring (this is done by using length(value)-length(replace(value,',','')) -- the number returned is how many non null columns we have in that column -- then we deduct the number from the known number of columns, calculated previously -- the +1 is added because there is no comma for single value SET @s = CONCAT('SELECT @numcolumns - (length(CONCAT_WS(\',\',', @x, '))-length(replace(CONCAT_WS(\',\',', @x, '),\',\',\'\')) + 1) FROM ',@db,'.',@tb,';'); PREPARE stmt FROM @s; EXECUTE stmt; -- after this execution we have returned for each row the number of null columns -- I will leave to you to add a sum() group call if you want to find the null values for the whole table DEALLOCATE PREPARE stmt;