A variation of the dynamic sql approach which outputs 3 columns (the count, the column name and the table name). You can specify the table name(s) you want in the where clause and you can optionally also filter which columns you want to consider.

DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'
UNION ALL
SELECT ''' + t.name + ''' as tname '
  +  ', ''' + c.name + ''' as cname '
  + ', COUNT(' + QUOTENAME(c.name) + ') as rows '
  + ' FROM ' + t.name 
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.name IN (N'foo')
--AND c.name LIKE N'columnname%'
  ;

SET @sql = STUFF(@sql, 1, 11, N'');
-- select @sql
EXEC (@sql)

This variant does not include "where [colname] IS NOT NULL" as this is redundant given the COUNT() will just ignore NULL values and it is unlikely that these where clauses will enhance overall performance as table scans are likely in all or most cases.

see example: https://dbfiddle.uk/EAez4OGR

You can use a dynamic SQL query to achieve this. Here’s an example that you can modify to fit your needs:

DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'
UNION ALL
SELECT COUNT(' + QUOTENAME(c.name) + ') FROM schema.tablename WHERE ' + QUOTENAME(c.name) + ' IS NOT NULL'
FROM sys.columns AS c
WHERE c.[object_id] = OBJECT_ID(N'schema.tablename')
AND c.name LIKE 'columnname%';

SET @sql = STUFF(@sql, 1, 11, N'');

--PRINT @sql;
EXEC sp_executesql @sql;

This code generates a dynamic SQL query that counts the non-null values in each column of the schema.tablename table whose name starts with columnname . The results are then combined using UNION ALL . You can modify the WHERE clause in the SELECT statement to include only the columns you want to count.

Here's an updated code snippet:

CREATE TABLE #temp_counts (column_name VARCHAR(255), count INT);

DECLARE @i INT = 1;
DECLARE @col_name VARCHAR(255);
DECLARE @query VARCHAR(MAX);

WHILE @i <= 15
BEGIN
SET @col_name = CONCAT('columnname', @i);
SET @query = CONCAT('SELECT ''', @col_name, ''' AS column_name, COUNT(', QUOTENAME(@col_name), ') AS count FROM schema.tablename WHERE ', QUOTENAME(@col_name), ' IS NOT NULL;');

INSERT INTO #temp_counts
EXEC(@query);

SET @i = @i + 1;
END

SELECT *
INTO schema.counts_table
FROM #temp_counts
ORDER BY column_name;

DROP TABLE #temp_counts;

In addition to using CREATE TABLE #temp_table syntax, I've also updated the code to use QUOTENAME() function to properly escape the column names in the queries. This can help prevent SQL injection attacks and ensure that the code is more robust.