Count of Null Values in Each column of a table

Question

Hi ,

I have a requirement to create an output for 70 tables , each having more than 10 cloumns , and generate a file with schema name, tablename, total number of rows, and number or null values in each column (most important is the count of null values).

So far I have tried hardcoding the table name and column name. This SQL is working.

DECLARE @SchemaName NVARCHAR(128) = 'dbo'; -- Schema name

DECLARE @TableName NVARCHAR(128) = 'T_SI_ETAT'; -- Replace with your table name

DECLARE @ColumnName NVARCHAR(128) = 'TSE_SERVICES'; -- Replace with your column name

DECLARE @SQL NVARCHAR(MAX);

SET @SQL = '

SELECT

''' + @SchemaName + ''' AS SchemaName,

''' + @TableName +''' AS TableName,

''' + @ColumnName +''' As ColumnName,

SUM(CASE WHEN [' + @ColumnName + '] IS NULL THEN 1 ELSE 0 END) AS RowsWithNullValues,

COUNT(*) AS TotalRows

FROM

' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ';';

EXEC sp_executesql @SQL;

However, I want to create a dynamic SQL to pick up all the columns of a table ( so that I dont have to mention all the column names).

Is there a way to do it.

Any help is much appreciated

Thank you

Answer 1

Hi @Nik,

Please try the following solution as a starting point.

USE tempdb;
GO
DROP TABLE IF EXISTS #tmpTable;
-- DDL and sample data population, start
CREATE TABLE #tmpTable  (ID INT IDENTITY PRIMARY KEY, Col_1 VARCHAR(20), Col_2 VARCHAR(20), Col_3 VARCHAR(20), Col_4 VARCHAR(20));
INSERT INTO #tmpTable (Col_1, Col_2, Col_3, Col_4) VALUES
(NULL, 'Nike', 'Adidas', NULL),
(NULL, 'Nike', 'Adidas', 'Asics'),
(NULL, 'Asics', NULL, 'Asics'),
(NULL, 'Nike', NULL, 'Adidas');
-- DDL and sample data population, end
DECLARE @total_row_counter INT = (SELECT count(*) from tempdb..#tmpTable);
;WITH rs AS
(
	SELECT c.value('local-name(.)', 'sysname') AS column_name
	FROM #tmpTable AS t
	CROSS APPLY (SELECT t.* FOR XML PATH(''), TYPE) AS t1(x)
	CROSS APPLY x.nodes('/*') AS t2(c)
), rs2 AS
(
	(
	SELECT name AS column_name, NULL AS row_counter_with_values
	FROM tempdb.sys.columns
	WHERE object_id = object_id('tempdb..#tmpTable')
	   AND is_nullable = 1
	EXCEPT
	SELECT column_name, NULL
	FROM rs
	)
	UNION ALL
	SELECT column_name, COUNT(*) AS row_counter_with_values
	FROM rs
	GROUP BY column_name
)
SELECT * 
	, @total_row_counter - COALESCE(row_counter_with_values,0) AS row_counter_with_nulls
	, @total_row_counter AS total_row_counter 
FROM rs2;