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