SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Table1 (
    col1 VARCHAR(100) NOT NULL,
    col2 VARCHAR(100)
);
  
INSERT INTO Table1
VALUES
    ('', NULL),
    ('A', ''),
    ('B', ' '),
    ('C', 'CC');
4 rows affected

CREATE SCHEMA [Some Schema];
CREATE TABLE [Some Schema].[Table Ω] (
    [col α] DATETIME NOT NULL,
    [col β] INT,
    [col γ] INT NOT NULL
);

INSERT INTO [Some Schema].[Table Ω]
VALUES
    (GETDATE(), NULL, 1),
    ('1900-01-01 00:00:00', 123, 0);
2 rows affected

SELECT C.ORDINAL_POSITION, C.COLUMN_NAME, ISNULL(NBC.NonBlankCount, 0) AS NonBlankCount
FROM INFORMATION_SCHEMA.COLUMNS C
LEFT JOIN (
    SELECT NB.ORDINAL_POSITION, COUNT(*) AS NonBlankCount
    FROM [dbo].[Table1] T
    CROSS APPLY (
        SELECT 0 AS ORDINAL_POSITION WHERE 1 = 0 -- Empty result to seed the union
        UNION ALL SELECT 1 WHERE 1 = 1 AND T.[col1] IS NOT NULL AND T.[col1] <> ''
        UNION ALL SELECT 2 WHERE 1 = 1 AND T.[col2] IS NOT NULL AND T.[col2] <> ''
    ) NB
    GROUP BY NB.ORDINAL_POSITION
) NBC
    ON NBC.ORDINAL_POSITION = C.ORDINAL_POSITION
WHERE C.TABLE_SCHEMA = N'dbo'
AND C.TABLE_NAME = N'Table1'
ORDER BY C.ORDINAL_POSITION;


ORDINAL_POSITION
COLUMN_NAME
NonBlankCount


1
col1
3

2
col2
1


CREATE TABLE #ColumnValueCounts (
    TABLE_CATALOG SYSNAME,
    TABLE_SCHEMA SYSNAME,
    TABLE_NAME SYSNAME,
    COLUMN_NAME SYSNAME,
    ORDINAL_POSITION INT,
    DATA_TYPE SYSNAME,
    NON_BLANK_COUNT INT
);
DECLARE table_cursor CURSOR FOR
    SELECT TABLE_SCHEMA, TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE';

DECLARE @TableSchema SYSNAME;
DECLARE @TableName SYSNAME;

OPEN table_cursor;

FETCH NEXT FROM table_cursor INTO @TableSchema, @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE @ColumnList NVARCHAR(MAX) = (
        SELECT STRING_AGG(CAST(S.SelectStatement AS NVARCHAR(MAX)),
                          CHAR(13) + CHAR(10))
                          WITHIN GROUP(ORDER BY C.ORDINAL_POSITION)
        FROM INFORMATION_SCHEMA.COLUMNS C
        CROSS APPLY (
            SELECT CONCAT(
                SPACE(16),
                'UNION ALL SELECT ',
                C.ORDINAL_POSITION,
                ' WHERE 1 = 1',
                CASE WHEN C.IS_NULLABLE = 'YES'
                     THEN ' AND T.' + QUOTENAME(C.COLUMN_NAME) + ' IS NOT NULL'
                     ELSE '' END,
                CASE WHEN C.DATA_TYPE IN ('date', 'datetime', 'datetime2',
                                          'smalldatetime', 'datetimeoffset')
                     THEN ' AND T.' + QUOTENAME(C.COLUMN_NAME) + ' <> ''1900-01-01'''
                     ELSE '' END,
                CASE WHEN C.DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar')
                     THEN ' AND DATALENGTH(T.' + QUOTENAME(C.COLUMN_NAME) + ') > 0'
                     ELSE '' END,
                ' -- ', QUOTENAME(C.COLUMN_NAME), ' ', QUOTENAME(C.DATA_TYPE),
                CASE WHEN C.IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END
                ) AS SelectStatement
        ) S
        WHERE C.TABLE_SCHEMA = @TableSchema
        AND C.TABLE_NAME = @TableName
    );
    
    DECLARE @Sql NVARCHAR(MAX) = '
        INSERT INTO #ColumnValueCounts 
            (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,
             COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE,
             NON_BLANK_COUNT)
        SELECT
            C.TABLE_CATALOG, C.TABLE_SCHEMA, C.TABLE_NAME,
            C.COLUMN_NAME, C.ORDINAL_POSITION, DATA_TYPE,
            ISNULL(NBC.NonBlankCount, 0)
        FROM INFORMATION_SCHEMA.COLUMNS C
        LEFT JOIN (
            SELECT NB.ORDINAL_POSITION, COUNT(*) AS NonBlankCount
            FROM ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) + ' T
            CROSS APPLY (
                SELECT 0 AS ORDINAL_POSITION WHERE 1 = 0 -- Empty result to seed the union
' + @ColumnList + '
            ) NB
            GROUP BY NB.ORDINAL_POSITION
        ) NBC
            ON NBC.ORDINAL_POSITION = C.ORDINAL_POSITION
        WHERE C.TABLE_SCHEMA = N' + QUOTENAME(@TableSchema, '''') + '
        AND C.TABLE_NAME = N' + QUOTENAME(@TableName, '''') + '
        ORDER BY C.ORDINAL_POSITION;
    ';
    
    PRINT @Sql;
    EXEC sp_executesql @Sql;

    FETCH NEXT FROM table_cursor INTO @TableSchema, @TableName;
END;

CLOSE table_cursor;
DEALLOCATE table_cursor;

        INSERT INTO #ColumnValueCounts 
            (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,
             COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE,
             NON_BLANK_COUNT)
        SELECT
            C.TABLE_CATALOG, C.TABLE_SCHEMA, C.TABLE_NAME,
            C.COLUMN_NAME, C.ORDINAL_POSITION, DATA_TYPE,
            ISNULL(NBC.NonBlankCount, 0)
        FROM INFORMATION_SCHEMA.COLUMNS C
        LEFT JOIN (
            SELECT NB.ORDINAL_POSITION, COUNT(*) AS NonBlankCount
            FROM [dbo].[Table1] T
            CROSS APPLY (
                SELECT 0 AS ORDINAL_POSITION WHERE 1 = 0 -- Empty result to seed the union
                UNION ALL SELECT 1 WHERE 1 = 1 AND DATALENGTH(T.[col1]) > 0 -- [col1] [varchar] NOT NULL
                UNION ALL SELECT 2 WHERE 1 = 1 AND T.[col2] IS NOT NULL AND DATALENGTH(T.[col2]) > 0 -- [col2] [varchar]
            ) NB
            GROUP BY NB.ORDINAL_POSITION
        ) NBC
            ON NBC.ORDINAL_POSITION = C.ORDINAL_POSITION
        WHERE C.TABLE_SCHEMA = N'dbo'
        AND C.TABLE_NAME = N'Table1'
        ORDER BY C.ORDINAL_POSITION;
    

        INSERT INTO #ColumnValueCounts 
            (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,
             COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE,
             NON_BLANK_COUNT)
        SELECT
            C.TABLE_CATALOG, C.TABLE_SCHEMA, C.TABLE_NAME,
            C.COLUMN_NAME, C.ORDINAL_POSITION, DATA_TYPE,
            ISNULL(NBC.NonBlankCount, 0)
        FROM INFORMATION_SCHEMA.COLUMNS C
        LEFT JOIN (
            SELECT NB.ORDINAL_POSITION, COUNT(*) AS NonBlankCount
            FROM [Some Schema].[Table Ω] T
            CROSS APPLY (
                SELECT 0 AS ORDINAL_POSITION WHERE 1 = 0 -- Empty result to seed the union
                UNION ALL SELECT 1 WHERE 1 = 1 AND T.[col α] <> '1900-01-01' -- [col α] [datetime] NOT NULL
                UNION ALL SELECT 2 WHERE 1 = 1 AND T.[col β] IS NOT NULL -- [col β] [int]
                UNION ALL SELECT 3 WHERE 1 = 1 -- [col γ] [int] NOT NULL
            ) NB
            GROUP BY NB.ORDINAL_POSITION
        ) NBC
            ON NBC.ORDINAL_POSITION = C.ORDINAL_POSITION
        WHERE C.TABLE_SCHEMA = N'Some Schema'
        AND C.TABLE_NAME = N'Table Ω'
        ORDER BY C.ORDINAL_POSITION;
    
5 rows affected

SELECT *
FROM #ColumnValueCounts
ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;


TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
ORDINAL_POSITION
DATA_TYPE
NON_BLANK_COUNT


fiddle_21e8acb661e84a12a789ecc7a71e8259
dbo
Table1
col1
1
varchar
3

fiddle_21e8acb661e84a12a789ecc7a71e8259
dbo
Table1
col2
2
varchar
2

fiddle_21e8acb661e84a12a789ecc7a71e8259
Some Schema
Table Ω
col α
1
datetime
1

fiddle_21e8acb661e84a12a789ecc7a71e8259
Some Schema
Table Ω
col β
2
int
1

fiddle_21e8acb661e84a12a789ecc7a71e8259
Some Schema
Table Ω
col γ
3
int
2
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	DATA_TYPE	NON_BLANK_COUNT
fiddle_21e8acb661e84a12a789ecc7a71e8259	dbo	Table1	col1	1	varchar	3
fiddle_21e8acb661e84a12a789ecc7a71e8259	dbo	Table1	col2	2	varchar	2
fiddle_21e8acb661e84a12a789ecc7a71e8259	Some Schema	Table Ω	col α	1	datetime	1
fiddle_21e8acb661e84a12a789ecc7a71e8259	Some Schema	Table Ω	col β	2	int	1
fiddle_21e8acb661e84a12a789ecc7a71e8259	Some Schema	Table Ω	col γ	3	int	2