Sql: Count Null and Non-Null values for set of tables and fields dynamically

Question

I would like to create dynamic sql query where for the set of tables and fields from information schema I will count null and non-null values of these fields.

I have idea to use it something like this (if I am not mistaking)

select distinct DB_NAME() as db_n, 
       TABLE_NAME,
       COLUMN_NAME,
       DATA_TYPE as DataType
        
     into #baseNew
from INFORMATION_SCHEMA.COLUMNS
where left(COLUMN_NAME,4) ='new_'
order by TABLE_NAME,COLUMN_NAME

this next part that should be dynamic would be something like this

select distinct b.db_n,
                TABLE_NAME,
       COLUMN_NAME,
                (select case when column_name is null then 1 else 0 end as NullValues
                        from table_name) NullValues,
                (select case when column_name is not null then 1 else 0 end as NotNullValues,
                from table_name) NotNullValues,
                (select count(1) as numRows from table_name) TotalRows
       
  
  from #baseNew b

(and probably insert in some table or data frame for python)

And if that is possible to be run in python script to be run on multiple DBs

Answer 1

The best way to do this would be to construct the query in one shot using STRING_AGG and sys.columns (you should avoid INFORMATION_SCHEMA, it's for compatibility only).

We start off with a basic query for say: Table1 (Col1, Col2), remember that COUNT(something) only counts up non-null values

SELECT
  TableName = 'Table1',
  TotalRows = COUNT(*),
  Col1 = COUNT(Col1),
  Col2 = COUNT(Col2)
FROM Table1

We then unpivot that:

SELECT
  TableName,
  TotalRows,
  NonNullsRows,
  NullsRows = TotalRows - NonNullsRows
FROM (
    SELECT
      TableName = 'Table1',
      TotalRows = COUNT(*),
      Col1 = COUNT(Col1),
      Col2 = COUNT(Col2)
    FROM Table1
) t
UNPIVOT (NonNullsRows FOR Col IN (
    Col1, Col2
)) p

We can then dynamically construct the above for all tables and columns

DECLARE @sql nvarchar(max) =
(
    SELECT STRING_AGG(TableQuery, N'
UNION ALL
')
    FROM (
        SELECT N'
SELECT
  TableName,
  Col as ColumnName,
  TotalRows,
  NonNullsRows,
  NullsRows = TotalRows - NonNullsRows
FROM (
    SELECT
      TableName = ' + QUOTENAME(t.name, '''') + N',
      TotalRows = COUNT(*),
      ' + STRING_AGG(CAST(QUOTENAME(c.name) + ' = COUNT(' + QUOTENAME(c.name) + ')' AS nvarchar(max)), ',') + N'
    FROM ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) + N'
) t
UNPIVOT (NonNullsRows FOR Col IN 
    (' + STRING_AGG(CAST(QUOTENAME(c.name) AS nvarchar(max)), ',') + N'
)) p'

        FROM sys.tables t
        JOIN sys.columns c ON c.object_id = t.object_id
        WHERE c.name LIKE 'new_'   -- do you want this filter
           -- you may also want AND c.is_nullable = 1
        GROUP BY t.object_id, t.name, t.schema_id

    ) AS t(TableQuery)
);

PRINT @sql;  -- for testing

EXEC sp_executesql @sql;