Sql: Count Null and Non-Null values for set of tables and fields dynamically 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
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,
  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 (NonNullRows 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;